Link to home
Create AccountLog in
Avatar of Iver Erling Arva
Iver Erling ArvaFlag for Norway

asked on

passing parameter into pl/sql sub query

select distinct
       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

Open in new window

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.

Please help! Thx!
Avatar of johnsone
Flag of United States of America image

Not sure why you need the subquery at all.  You also had the AGREEMENT_LINE table in the FROM clause with no joins to it, were you creating that Cartesian product on purpose?

SELECT DISTINCT p.policy_no, 
                al.product_line_id EPH01 
FROM   tia.policy p, 
       tia.agreement_line al 
WHERE  p.policy_status = 'P' 
       AND p.policy_no = al.policy_no 
       AND al.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' 

Open in new window

The reason you are getting the error is because Oracle needs to materialize the sub-query before trying to run the rest of the query (basically the sub query has to run first).  Because of this, the POLICY_NO field is not available to use in the where clause of the sub-query.
Avatar of Iver Erling Arva


So, can I do it the other way around then and have the FROM TIA.POLICY P table reference in the subquery and point to it from the main query, then?

The reason why I do it like this is that I have limited priviledges on the database and the only thing I can do is to use SELECT. The data I am looing for is stored in the same column and on different rows in the database

Cusno Name  Policy Product
12345 IVerE 654321 ABC01
12345 IVerE 654321 DEF01
12345 IVerE 654321 JKL01

and I want to do a SELECT that returns

Cusno Name  Policy Prod1 Prod2  Prod3 Prod4
12345 IVerE 654321 ABC01 DEF01        JKL01

Open in new window

Avatar of johnsone
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I am searching through a large file with clients. Each client is listed on several rows in the table, once per product. I want to show one line per client and each product in a column of its own. Will the above approach accomplish this? I am not just looking for one client, I'm looking for hundreds...