Home » SQL & PL/SQL » SQL & PL/SQL » Input tables values created by 1st query to 2nd query
Input tables values created by 1st query to 2nd query [message #251200] Thu, 12 July 2007 12:25 Go to next message
hmarc
Messages: 5
Registered: July 2007
Junior Member
I am using SQL Plus and created a table with 1 column and 321 rows by using CREATE TABLE AS. It contains 321 page names.

I would like to run a second query using this table as input. The 2nd query is written and works but I have to manually enter the page names.

How can I pass the page names to the second query? Variables, subscripts, indexes?

Thanks in advance,

Hal
Re: Input tables values created by 1st query to 2nd query [message #251202 is a reply to message #251200] Thu, 12 July 2007 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT

Regards
Michel
Re: Input tables values created by 1st query to 2nd query [message #251208 is a reply to message #251200] Thu, 12 July 2007 12:59 Go to previous messageGo to next message
hmarc
Messages: 5
Registered: July 2007
Junior Member
I am trying to use the code below but pull from the table called my_panels instead of specifying the page name like 'CI_CUST_CL_ALG'.

SELECT DISTINCT D.ROLEUSER, D.DESCR, B.ROLENAME, A.CLASSID, A.PNLITEMNAME, A.DISPLAYONLY, F.BARITEMNAME
FROM PSAUTHITEM A,PSROLECLASS B,PSROLEUSER C,PSROLEUSER_SRCH D, PSOPRDEFN E, hal_panel F
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND C.ROLEUSER = D.ROLEUSER
AND D.ROLEUSER = E.OPRID
AND A.PNLITEMNAME = 'CI_CUST_CL_ALG'
AND A.DISPLAYONLY = '0'
AND E.ACCTLOCK='0'
ORDER BY B.ROLENAME;
Re: Input tables values created by 1st query to 2nd query [message #251218 is a reply to message #251208] Thu, 12 July 2007 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I said, use SELECT:
"AND A.PNLITEMNAME in (select ... from my_panels)"
or the like.

Regards
Michel

[Updated on: Thu, 12 July 2007 13:17]

Report message to a moderator

Re: Input tables values created by 1st query to 2nd query [message #251223 is a reply to message #251218] Thu, 12 July 2007 13:13 Go to previous messageGo to next message
hmarc
Messages: 5
Registered: July 2007
Junior Member
Got you on SELECT. Let me try to rephrase. I want the second query to pull each row in the my_panels table. All 321 rows.
Re: Input tables values created by 1st query to 2nd query [message #251227 is a reply to message #251218] Thu, 12 July 2007 13:15 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Thu, 12 July 2007 13:07
This is what said, use SELECT:
"AND A.PNLITEMNAME in (select ... from my_panels)"
or the like.




Why won't the above give you all the rows ?

[Updated on: Thu, 12 July 2007 13:16]

Report message to a moderator

Re: Input tables values created by 1st query to 2nd query [message #251228 is a reply to message #251223] Thu, 12 July 2007 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I clearly don't understand you.
Build an example.
Create 2 tables t1 and t2. Fill them with say 5 rows and tell us what you want from this example.

Regards
Michel
Re: Input tables values created by 1st query to 2nd query [message #251233 is a reply to message #251227] Thu, 12 July 2007 13:20 Go to previous messageGo to next message
hmarc
Messages: 5
Registered: July 2007
Junior Member
thx
Re: Input tables values created by 1st query to 2nd query [message #251259 is a reply to message #251233] Thu, 12 July 2007 14:30 Go to previous message
hmarc
Messages: 5
Registered: July 2007
Junior Member
The problem is I am a Novice. I was making it more difficult than it was. It works. Thanks for the assistance.

CODE
SELECT DISTINCT D.ROLEUSER, D.DESCR, B.ROLENAME, A.CLASSID, A.PNLITEMNAME, A.DISPLAYONLY, F.BARITEMNAME
FROM PSAUTHITEM A,PSROLECLASS B,PSROLEUSER C,PSROLEUSER_SRCH D, PSOPRDEFN E, HAL_PANEL F
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND C.ROLEUSER = D.ROLEUSER
AND D.ROLEUSER = E.OPRID
AND A.PNLITEMNAME = F.BARITEMNAME
AND A.DISPLAYONLY = '0'
AND E.ACCTLOCK='0'
ORDER BY B.ROLENAME;





/CODE
Previous Topic: Dynamic Search Queries
Next Topic: Function Overloading
Goto Forum:
  


Current Time: Sat Dec 10 01:13:26 CST 2016

Total time taken to generate the page: 0.13271 seconds