Hi,
I have records with ID nos and bunch of NULL values in ID columns. For example ID nos range from 1 to 1000 and after that NULL values for another 500 records (Number of records vary).
For the records with NULL values, i have to insert numbers from 1 to 500 (till end record).
Can this be done using Key_generation? or any other method. gen_row_num function is not working as it mixes all the records together.
Appreciate your help.
Arun
Arun.K 
(BOB member since 2011-10-18)
case off your null records and use row_gen on just those.
then merge them back together.
jlynn73 (BOB member since 2009-10-27)
What rule would you apply to this key?
ifthenelse(KEY is not null, KEY, ???)
What should the else-part be?
Werner Daehn 
(BOB member since 2004-12-17)
The three fields in source are like this
COL1(upto 1500 records)
123
456
789
COL2 (upto 1000 records, the last 500 records i need to fill numbers sequentially from 1, 2,3 etc)
1
2
3
4
.
.
.
1000
COL3 values are ZMBO for 1 to 500 records and NULL for first 1 to 1000 records.
So output should look like this
COL1 … COL2…COL3
123…1…NULL
456…2…NULL
759…3…NULL
563…NULL
637…999…NULL
536…1000…NULL
568… 1…ZMBO
536…2… ZMBO
435…ZMBO
437…500…ZMBO
As suggested, If case works i can do that.
Arun
Arun.K (BOB member since 2011-10-18)
order the dataset - if required - and in a second query use the mapping
nvl(COL2, gen_row_num_per_group(COL3))
??
nvl() or ifthenelse(), both will work.
http://wiki.sdn.sap.com/wiki/x/N4M_E
Werner Daehn (BOB member since 2004-12-17)
Thanks Werner. You are very helpful.
As suggested i did an orderby based on the column and did nvl()
It works just fine.
Arun
Arun.K (BOB member since 2011-10-18)