Table :
create table dummy(z number,a number ,b number,c number,d date,e number,f varchar2(10),g number,h number, primary key(z,f));
insert into dummy values(1,null,null,2,systimestamp,3,'ABC',4,5);
insert into dummy values(1,10,null,2,systimestamp,3,'DEF',4,5);
insert into dummy values(1,null,20,2,systimestamp,3,'HIJ',4,5);
I have a table where i need to get the values based on priority and based on values
Example :
First priority is column f
'ABC'->1st priority
'DEF'->second priority
'HIJ'->Third priority
This table can only have above three values
next priority is values in the columns a,b,c,d,e,h
Output i want
Z
| A | B
| C | D | E
| F | G
| H
|
|---|
| 1 | 10 | 20 | 2 | SYSTIMESTAMP | 3 | ABC | 4 | 5 |
Based on priority 'ABC' should come first but since the column values are null i should look for other column values in the next prioritized row of F and see if the values exist or not and then select the column value.
There is a scenario where A is null so i fetched 10 from the next row where z=1 and f='DEF'.
Another scenario where B is null for z=1 and f='DEF' in that case i should look for another source HIJ where the value is 20 so i should pick it from that column.
Thanks in advance.