[SFDonovan]

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

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.

[shammat]

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.

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