Thanks a lot for your suggestion but I think it will not work in the following query

select
(SELECT unit_cost
FROM price_hist rh1
WHERE rh1.loc in ( case
When ps.zone_node_type= 0
then
ps.location
when ps.zone_node_type = 1
then
(select location from rpm_zone_location where zone_id=ps.zone_id)
end
) as Cost
from ps

In this case i need to return all the location associated to Zone_id and map the location with rh1.loc and then select unit_cost for them.

select
(SELECT unit_cost
FROM price_hist rh1
WHERE case when ps.zone_node_type= 0 then rh1.loc end =ps.location
and case when ps.zone_node_type= 1 then rh1.loc end in (select location from rpm_zone_location where zone_id=ps.zone_id)
) unit_cost
from PS ps