Forum Stats

  • 3,759,181 Users
  • 2,251,510 Discussions
  • 7,870,526 Comments

Discussions

Generating multiple rows of fixed length output for each record

3244859
3244859 Member Posts: 4
edited Jun 13, 2016 1:15PM in SQL & PL/SQL

I have a table that has a lot of information. you can call it a FACT table if you want to.Sample example below


Table F

oidPnoPdatePidaddrsPh:noAmtFlagcompanyemployee_FK1_FK2_FK3_FK4_FK5_FK6
100101/01/201323xyz333333334YgreatDOC151411
200201/01/201425pqr222222238NNewSOS12

4

122

I want to create a SQl that generates an output which splits each unique record in the FACT into multiple fixed length rows.Like Below

'SQ1'||001                           23                                                     great                                        DOC ----> length is 100. Pno is in field 3 to 5,Pid in field 15 to 16. company name in field 56 to 70

'SQ2'||001           'Y'                          'DOC'          34         'XYZ'                            3333333                         ----->length is 120

***********************************similar format for 2nd record below****************************************************************** 

'SQ1'||002                           25                                                     New                                       SOS----> length is 100,

'SQ2'||002           'Y'                          'SOS'          38         'PQR'                            2222222                         ----->length is 120


continue displaying the split records for the selected records in the fact

    

 

Tagged:

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jun 12, 2016 4:52PM
    3244859 wrote:
    
    I have a table that has a lot of information. you can call it a FACT table if you want to.Sample example below
    
    Table F oidPnoPdatePidaddrsPh:noAmtFlagcompanyemployee_FK1_FK2_FK3_FK4_FK5_FK6 1 001 01/01/2013 23 xyz 3333333 34 Y great DOC 1 5 1 4 1 1 2 002 01/01/2014 25 pqr 2222222 38 N New SOS 1 2 4 1 2 2 I want to create a SQl that generates an output which splits each unique record in the FACT into multiple fixed length rows.Like Below 'SQ1'||001                           23                                                     great                                        DOC ----> length is 100. Pno is in field 3 to 5,Pid in field 15 to 16. company name in field 56 to 70 'SQ2'||001           'Y'                          'DOC'          34         'XYZ'                            3333333                         ----->length is 120 ***********************************similar format for 2nd record below******************************************************************  'SQ1'||002                           25                                                     New                                       SOS----> length is 100, 'SQ2'||002           'Y'                          'SOS'          38         'PQR'                            2222222                         ----->length is 120
    continue displaying the split records for the selected records in the fact
          

    How do I ask a question on the forums?

    https://community.oracle.com/message/9362002#9362002

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,571 Red Diamond
    edited Jun 13, 2016 1:42AM

    If you want to format the SQL projection as a line of text, with columns at fixed positions in the line, then the RPAD() and LPAD() functions can be used.

    Note that the line of text as a SQL varchar2, is restricted to 4000 bytes.

    Here is a basic example:

    SQL> select
      2          '----|----1----|----2----|---3-----|---4-----|----5----|'       as LINE
      3  from       dual
      4  union all
      5  select
      6          rpad(empno,10)||
      7          rpad(ename,15)||
      8          rpad(job,20)||
      9          rpad(to_char(hiredate,'yyyy-mm-dd'),10)
     10  from       emp
     11  /
    
    LINE
    ------------------------------------------------------------
    ----|----1----|----2----|---3-----|---4-----|----5----|
    7369      SMITH          CLERK               1980-12-17
    7499      ALLEN          SALESMAN            1981-02-20
    7521      WARD           SALESMAN            1981-02-22
    7566      JONES          MANAGER             1981-04-02
    7654      MARTIN         SALESMAN            1981-09-28
    7698      BLAKE          MANAGER             1981-05-01
    7782      CLARK          MANAGER             1981-06-09
    7788      SCOTT          ANALYST             1987-04-19
    7839      KING           PRESIDENT           1981-11-17
    7844      TURNER         SALESMAN            1981-09-08
    7876      ADAMS          CLERK               1987-05-23
    7900      JAMES          CLERK               1981-12-03
    7902      FORD           ANALYST             1981-12-03
    7934      MILLER         CLERK               1982-01-23
    
    15 rows selected.
    
    SQL> 
    

    For 2 lines per row, a second SQL select against the same table is needed (via a UNION ALL), where this select does the other formatted line wanted. Keep in mind that this is not optimal as the same table is hit twice - bad for performance. And if you/someone else want to argue of throwing a line feed character into the SQL projection - shoddy approach as that relies on the client to treat the SQL projection in a certain way, and will fail for the majority of clients out there.

    Keep in mind that this type of formatting is typically used for data exchange, or reporting/printing. In which case is it better to deal with this formatting in the client language - not in SQL.

  • 3244859
    3244859 Member Posts: 4
    edited Jun 13, 2016 1:15PM

    Thanks Billy:

    I was aware of this option and its issues with performance.I was looking to see if there is a work around using SQL. I understand SQL isn't the right approach to achieve this But I dont have any other option.

This discussion has been closed.