hi all, i have a table that has the following data
WITH sec AS
(
SELECT 111 id, 'XPE' cid, 10 alias, 'TLC' Description, 'PEYU' lvl_1, 'IES' lvl_2, ' BAN' lvl_3 FROM dual UNION ALL
SELECT 111 id, 'XPE' cid, 78 alias, 'TLC2' Description, 'SUN' lvl_1, null lvl_2, ' BAN3' lvl_3 FROM dual UNION ALL
SELECT 111 id, 'XPE' cid, 200 alias, 'TLC' Description, null lvl_1, null lvl_2, ' BAN' lvl_3 FROM dual UNION ALL
SELECT 111 id, 'XPE' cid, 54 alias, 'TLC3' Description, 'SUN5' lvl_1, null lvl_2, ' BAN3' lvl_3 FROM dual UNION ALL
SELECT 112 id, 'XPE2' cid, 10 alias, 'PAD' Description, 'PEYU' lvl_1, 'IES' lvl_2, ' BAN' lvl_3 FROM dual UNION ALL
SELECT 112 id, 'XPE2' cid, 78 alias, 'PAD2' Description, 'SUN' lvl_1, null lvl_2, ' BAN3' lvl_3 FROM dual UNION ALL
SELECT 112 id, 'XPE2' cid, 54 alias, 'TLC3' Description, 'SUN5' lvl_1, null lvl_2, ' BAN3' lvl_3 FROM dual UNION ALL
SELECT 112 id, 'XPE2' cid, 200 alias, 'PAD' Description, 'SIN' lvl_1, 'CON' lvl_2, ' UIE' lvl_3 FROM dual UNION ALL
SELECT 113 id, 'XPE3' cid, 10 alias, 'PAD5' Description, NULL lvl_1, NULL lvl_2, NULL lvl_3 FROM dual UNION ALL
SELECT 113 id, 'XPE3' cid, 200 alias, 'PAD5' Description, NULL lvl_1, NULL lvl_2, NULL lvl_3 FROM dual UNION ALL
SELECT 113 id, 'XPE3' cid, 78 alias, 'PAD7' Description, 'SUN' lvl_1, null lvl_2, ' BAN3' lvl_3 FROM dual UNION ALL
SELECT 113 id, 'XPE3' cid, 54 alias, 'TLE' Description, 'SUN5' lvl_1, null lvl_2, ' BAN3' lvl_3 FROM dual
)
i want to write a query that give me this output
ID CID ALIAS DESCRIPTION LVL_1 LVL_2 LVL_3
111 XPE 10 TLC PEYU IES BAN
111 XPE 200 TLC PEYU IES BAN
111 XPE 78 TLC2 SUN BAN3
111 XPE 54 TLC3 SUN5 BAN3
112 XPE2 10 PAD PEYU IES BAN
112 XPE2 200 PAD SIN CON UIE
112 XPE2 78 PAD2 SUN BAN3
112 XPE2 54 TLC3 SUN5 BAN3
113 XPE3 10 PAD5
113 XPE3 200 PAD5
113 XPE3 78 PAD7 SUN BAN3
113 XPE3 54 TLE SUN5 BAN3
this is how the logic works:
as you can see the table has many different set of ids, 111,112,113 and each id has a set of alias
for id 111 and alias 200 the sec table has null values for level 1 and level 2 columns. what i want to do
is that if any of the level1,level2 and level3 columns has null VALUES(FOR aliases=200) then the values should be taking from the ROW(s) with alias=10
in this case alias=200 display peyu and ies for level1 and level2 column. this values were taking from the row with alias=10 and id =111.
level3 stay the same since it is not null
for id 112 and alias =200, none of te level columns has null so the value stay the same as in the table
for id 113 alias 200, all level columns has null so the value should be taking from alias=10 id =113.
in this case the row with alias=10 has also null so both rows 200 and 10 gets display with null values
the other rows with different alias should just be display.
i tried using las_value analytic function but i was not successful. i am using ORACLE 9I and ignore null option in last_value function is not supported
the data in the table is not in any particular order.
can someone help write a query that gives me the output above. thanks