7 Replies Latest reply: Oct 15, 2012 4:18 AM by Marwim RSS

    Report Help!!!!!

    800849
      Hi

      We have a report requirement and we are creating the report through util package.We are using CHR(9) for separator.

      Actual data in the table-->

      REQUEST_ID     CUSTOMER_NAME     CUSTOMER_TYPE      CID_STATUS
      43          SOCIC TC     A FREEVER

      Report in excel data-->>

      REQUEST_ID     CUSTOMER_NAME     CUSTOMER_TYPE     CID_STATUS NEXTFIELD
      43          SOCIC TC     A FREEVER


      The isssue is that here the CUSTOMER_NAME field some data are move to CUSTOMER_TYPE filed and values moved next field in the excel.


      Please help me.

      Regards
      Das

      Edited by: 797846 on Oct 12, 2012 1:41 PM
        • 1. Re: Report Help!!!!!
          800849
          Hi,

          The data are looks like below.


          Actual data in the table-->

          REQUEST_ID     CUSTOMER_NAME     CUSTOMER_TYPE CID_STATUS
          43     SOCIC TC     A FREEVER

          Report in excel data-->>

          REQUEST_ID     CUSTOMER_NAME     CUSTOMER_TYPE     CID_STATUS NEXTFIELD
          43     SOCIC TC     A FREEVER

          Regards
          Das
          • 2. Re: Report Help!!!!!
            rp0428
            It's not clear what you are trying to do, how and where you got the data you are posting or what, if anything, this has to do with sql developer.
            >
            We have a report requirement and we are creating the report through util package.We are using CHR(9) for separator.
            . . .
            The isssue is that here the CUSTOMER_NAME field some data are move to CUSTOMER_TYPE filed and values moved next field in the excel.
            >
            Then you should examine your customer_name values for embedded TAB (0x09) characters.

            Any embedded TAB characters will be treated as separators and cause values to shift.
            select customer_name, dump(customer_name) from myTable where customer_name like '%' || chr(9) || '%'
            • 3. Re: Report Help!!!!!
              800849
              Hi,

              I got the issue .but how we can avoid it.

              Please suggest.

              Regards
              Das
              • 4. Re: Report Help!!!!!
                rp0428
                >
                I got the issue .but how we can avoid it.
                >
                That response is totally meaningless.

                Reread my last reply, answer the questions and provide the information ask for.
                • 5. Re: Report Help!!!!!
                  800849
                  Hi,

                  As i told you that we are sending the report to Biz as excel format and we are generating the report data in pl sql and the data are having some TAB charater value which causes shifted the values for 1 column to next column.

                  for example if the data like this

                  Customer_name ---TETE TEST1

                  Then the half of the data for the customer_name column are shifted to next column in the excel.

                  Please suggest me if we can display the whole values TETE TEST1 in a single column.

                  Regards
                  Das
                  • 6. Re: Report Help!!!!!
                    rp0428
                    >
                    Then the half of the data for the customer_name column are shifted to next column in the excel.
                    >
                    Yes - because as I said above
                    >
                    Any embedded TAB characters will be treated as separators and cause values to shift.
                    >
                    You are using TAB as a separator so ALL TABS ARE SEPERATORS!

                    Your only option is to replace embedded TAB characters with something else, e.g. a SPACE (' ')
                    If ENAME and JOB might have embedded TABs then you need to use the REPLACE function on them
                    select empno, replace(ename, chr(9), ' ') ename, replace(job, chr(9), ' ') job, mgr, hiredate, sal, comm, deptno from emp;
                    • 7. Re: Report Help!!!!!
                      Marwim
                      Your first mistake is
                      the report to Biz as excel format
                      You generate a CSV file. CSV is not EXCEL. It only happens to be a handy feature of Excel to recognize CSV files and to open them in a tabular display.
                      the data are having some TAB charater value which causes shifted the values for 1 column to next column
                      Put your data in double quotes
                      dataCHR(9)datapart1CHR(9)datapart2
                      
                      ->
                      
                      "data"CHR(9)"datapart1CHR(9)datapart2"
                      Now when opend with Excel it will treat anything within double quotes as a single data element, even when it includes the separator character.

                      Regards
                      Marcus