CODEWITHSUNDEEP
sqloracle-databasesql-loader
digdug
digdug

Reputation: 221

sql loader associating variable number physical records with a single physical record

I have the following data:

Aapple mango wood
Bpine tea orange
Bnuts blots match
Ajust another record

Now I want every record beginning with 'A' to be associated with record beginning with 'B' until another 'A' record or non-'B' record is encountered. For example from the above data, I would like to retrieve the following data(2 records),

mango tea
mango blots

The number of B records following an A record is variable, that is,(A record might be followed by any number of B records(3 in the data below).

Aapple mango wood
Bpine tea orange
Bnuts blots match
Basdf asdf  asdf
Ajust another record

So the resulting output would be

mango tea
mango blots
mango asdf

Is it possible to do the above using sql loader?. Any help/pointers would be most welcome.

Edit: I was thinking about using CONTINUEIF clause, but there doesn't seem to be way to eliminate the records that was retrieved earlier. For example, if I use,

CONTINUEIF NEXT PRESERVE(1)='B'

I would get "mango tea blots asdf" in one go and not

"mango|tea"
"mango|blots"
"mango|asdf"

Upvotes: 0

Views: 292

Answers (1)

Kevin Burton
Kevin Burton

Reputation: 11934

I think i would load the records to 2 seperate tables based on the record type identifier, see: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1005614

and use recnum to preserve the order

see: http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch06.htm

you can then transform the data in sql

SELECT 
    a.text,
    b.text,
    a.id,
    a.nxtid 
FROM 
(
    SELECT text,id, NVL(LEAD(seq,1) OVER (ORDER BY id),999999) AS NXTID
    FROM t1
) a
LEFT JOIN t2 B ON b.seq > a.id AND b.id < a.nxtid

Upvotes: 1

Related Questions