Generating multiple rows of fixed length output for each record

3244859

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

oid	Pno	Pdate	Pid	addrs	Ph:no	Amt	Flag	company	employee	_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

    

 

John Thorton

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

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

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.