returns ORA-00904: "P"."POLICY_NO": invalid identifier. How do I get that policy_no from the main query into my sub queries? This is a simplified version. I have several codes (like EPH01) that I want to put in separate columns. They are in different rows in the database right now, so I thought I'd add a sub-query for each of them, like above, to have them on one row, but in separate columns for each policy_no.
select distinct p.policy_no, eph.pli EPH01 from tia.policy p, tia.agreement_line al, (select product_line_id as pli from tia.agreement_line al where product_line_id = 'EPH01' and al.policy_no = p.policy_no and al.cancel_code = 0 and al.trans_code != 0 and al.newest = 'Y') eph where p.policy_status = 'P' and p.policy_no = al.policy_no ;
From novice to tech pro — start learning today.