June 11th, 2015, 11:51 AM
-
Subquery factoring WITH clause
I have over 2000 rows of student ID's in an excel file and I need to populate a query with all their bio data. first_name, last_name, email and such. If I put them in a with statement as strings, how do I reference them in the where clause? They are not identified as a field in the with clause but are emplid in the other oracle tables where I would gather my data from.
For instance...
Code:
WITH
stdnt AS
(Select '10831663' from dual
union
Select '10276677' from dual
union
Select '10000956' from dual
union
Select '10001109' from dual
union
Select '10001675' from dual
union
Select '10005183' from dual
union
Select '10006108' from dual
union
Select '10008502' from dual
union
Select '10010882' from dual
union
Select '10015126' from dual),
euid AS
(SELECT b.OPRID
FROM PSOPRDEFN b
WHERE stdnt.EMPLID = b.EMPLID
AND b.acctlock = 0
AND rownum = 1),
names AS
(SELECT last_name, middle_name, first_name
from PS_HCR_PER_NAME_I b
where b.emplid = stdnt.emplid
AND b.NAME_TYPE = 'PRF'
AND b.EFFDT = (
SELECT MAX(a1.EFFDT)
FROM PS_HCR_PER_NAME_I a1
WHERE b.EMPLID = a1.EMPLID
AND b.NAME_TYPE = a1.NAME_TYPE)
)
SELECT emplid
,oprid
,last_name
,middle_name
,first_name
from stdnt,
euid,
names
where stdnt.emplid = euid.emplid;
I get ORA-00904: "STDNT"."EMPLID": invalid identifier
This is a production database so I cannot insert, or update a table directly. Just trying to manually pull this data so I can export to a CSV.
June 11th, 2015, 03:00 PM
-
You need to give the column from the first CTE a name. You are also not selecting the emplid column in the second CTE so it won't be available there either.
Code:
WITH stdnt (emplid) AS (
Select ...
), euid AS (
SELECT b.OPRID, b.emplid
FROM PSOPRDEFN b
WHERE stdnt.EMPLID = b.EMPLID
AND b.acctlock = 0
AND rownum = 1
), ...
Comments on this post
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code
Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html