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!
Iver Erling ArvaSenior consultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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

johnsoneSenior Oracle DBACommented:
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 ArvaSenior consultantAuthor Commented:
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

johnsoneSenior Oracle DBACommented:
If you are looking for data in different rows, a subquery is still not entirely necessary.  Join to the table twice.  Using your original, it would look like this:
SELECT DISTINCT p.policy_no, 
                eph.product_line_id EPH01 
FROM   tia.policy p, 
       tia.agreement_line al,
       tia.agreement_line eph 
WHERE  p.policy_status = 'P' 
       AND p.policy_no = al.policy_no 
       AND p.policy_no = eph.policy_no 
       AND eph.product_line_id = 'EPH01' 
       AND eph.policy_no = p.policy_no 
       AND eph.cancel_code = 0 
       AND eph.trans_code != 0 
       AND eph.newest = 'Y' 

Open in new window

To me, it would appear you are missing join conditions in that statement, and I'm not sure why you need the table twice as you aren't using the second one.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Iver Erling ArvaSenior consultantAuthor Commented:
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.