TOP 1 or LIMIT 1 not working in a Correlated Subquery - SPS 11

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