Avatar of Iver Erling Arva
Iver Erling ArvaFlag for Norway 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!
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Iver Erling Arva

8/22/2022 - Mon
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.
ASKER
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
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Iver Erling Arva

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