hi everybody, I need help with a query
I have this dataset
with dataset as
(
select 'test' cname, 1 cid, 123 amount, 111 amount2 from dual union all
select 'test2' cname, 2 cid, 222 amount, 34 amount2 from dual union all
select 'test3' cname, 3 cid, 333 amount, 67 amount2 from dual union all
select 'test4' cname, 4 cid, 444 amount, 89 amount2 from dual union all
select 'test5' cname, 5 cid, 444 amount, 56 amount2 from dual union all
select 'test6' cname, 6 cid, 444 amount, 34 amount2 from dual
)
i am trying to apply the following formula to every row except line1:
amount - amount2 + amount(from line1 cname=test)
so the output given the data above should be:
cname cid total
test 1 0
test 2 311
test 3 389
test 4 478
test 5 512
test 6 533
i was trying to use the lag function LAG (amount,cid-1) OVER ( ORDER BY cid) to get the amount value from line1
this will work with the dataset above. however, i may not have all 6 lines in my tables.
therefore, i could have a dataset such as
with dataset2 as
(
select 'test' cname, 1 cid, 123 amount, 111 amount2 from dual union all
select 'test4' cname, 4 cid, 444 amount, 89 amount2 from dual union all
select 'test6' cname, 6 cid, 444 amount, 34 amount2 from dual
)
with this dataset, output should be
cname cid total
test 1 0
test 4 478
test 6 533
if i use LAG (amount,cid-1) OVER ( ORDER BY cid) with this dataset, it wont work due to cid-1 that i have inside the lag function
can someone help write a query to get the output above considering the two datasets.
with any dataset, the query should be able to get the amount value from line 1 regardless of what line oracle is currently processing
i guess cid is not good to use with lag function