#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    360
    Rep Power
    10

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    352
    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

    • SFDonovan agrees : outstanding
    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

IMN logo majestic logo threadwatch logo seochat tools logo