asked on

passing parameter into pl/sql sub query

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
;

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!

johnsone

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

johnsone

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.

Iver Erling Arva

ASKER

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
etc.

and I want to do a SELECT that returns

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

Open in new window

IVer

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Iver Erling Arva

ASKER

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...