Hi Friends,
I am trying to work on a query to fill the Price for a product where it is zero with non-zero value fro previous record. I tried to write a simple correlated subquery but its not working.
var_1 = select * from "XYZ"."PRD_TEST" where price <> 0 order by period desc;
var_out = select a.product,a.period, ( select price from :var_1 b where a.product = b.product and a.period > b.period and b.period <> 0 limit 1 ) as price from "XYZ"."PRD_TEST" a;
PRODUCT PERIOD PRICE
A 1 100
A 2 0 - to be filled with 100
A 3 0 - to be filled with 100
A 4 5
A 5 0 - to be filled with 5
I tried to replace the sub-query with scalar function but it does not take table as a Parameter.
I tried to achieve the output using Left outer join and Row_number but it's too expensive and runs for a long time.
I am looking for a best option to fetch only 1 record in the subquery just like TOP 1.
Thanks,
Gokul