Hello Tom.
I'm a keen long time reader of your asktom site and have a query which I'm struggling with.
I have a table which is stored like this:
create table T1 (
CMPY CHAR(2),
CODE_ID CHAR(1),
RNGE CHAR(8),
CODE_SIZE number(1),
FROM_TO_CNT number(1),
RNGE_DATA varchar2(240)
)
insert into t1 values('CA','A','L0001',8,1,'L9000 LZ999 ');
insert into t1 values('CA','A','L0002',8,1,'A9000 AZ999 ');
insert into t1 values('CA','A','L0003',8,1,'B9000 BZ999 ');
insert into t1 values('CA','A','L0004',8,1,'C9000 CZ999 ');
insert into t1 values('CA','A','L0005',8,1,'D9000000DZ999999');
insert into t1 values('CA','A','L0006',8,2,'E9000 EZ999 F900 F999 ');
insert into t1 values('CA','B','L0007',6,3,'K00001K00002K00010K00020L00010L00099');
SQL> select * from t1;
CM C RNGE CODE_SIZE FROM_TO_CNT RNGE_DATA
-- - -------- ---------- ----------- --------------------------------------------------
CA A L0001 8 1 L9000 LZ999
CA A L0002 8 1 A9000 AZ999
CA A L0003 8 1 B9000 BZ999
CA A L0004 8 1 C9000 CZ999
CA A L0005 8 1 D9000000DZ999999
CA A L0006 8 2 E9000 EZ999 F900 F999
CA B L0007 6 3 K00001K00002K00010K00020L00010L00099
7 rows selected.
So the structure of this is that I have this FROM_TO_CNT which tells me how many chunks of CODE_SIZE pairs I will find in my RNGE_DATA.
I really want to select this so that each pair of RNGE_DATA come out on a seperate row. I've tried thinking about pivots but come unstuck relating this to CODE_SIZE and FROM_TO_CNT
I'd like the output to look like (abridged)
CMPY CODE-ID RNGE FROM TO
CA A L0001 L9000 LZ999
CA A L0005 D9000000 DZ999999
CA A L0006 E9000 EZ999
CA A L0006 F900 F999
etc. With multiple FROM/TO pairs for each row where FROM_TO_CNT > 1
I appreciate that the format of the data is not ideal for this query which will become widely used as part of a view but I need to work with what I have ...
Any assistance you can provide would be much appreciated, thank you.
ops$tkyte%ORA10GR2> select cmpy, code_id, rnge,
2 substr( rnge_data, 1+COLUMN_VALUE*2*code_size, code_size ) from_,
3 substr( rnge_data, 1+code_size+COLUMN_VALUE*2*code_size, code_size ) to_
4 from t1, table( cast( multiset( select level-1 l from dual connect by level <= T1.FROM_TO_CNT ) as sys.odcinumberlist ) )
5 /
CM C RNGE FROM_ TO_
-- - -------- ---------- ----------
CA A L0001 L9000 LZ999
CA A L0002 A9000 AZ999
CA A L0003 B9000 BZ999
CA A L0004 C9000 CZ999
CA A L0005 D9000000 DZ999999
CA A L0006 E9000 EZ999
CA A L0006 F900 F999
CA B L0007 K00001 K00002
CA B L0007 K00010 K00020
CA B L0007 L00010 L00099
10 rows selected.
http://asktom.oracle.com/pls/asktom/asktom.search?p_string=sys.odcinumberlist for further examples/reading on the 'trick' there.