Re: Simple Query

[David Donnelly <Dave@ISISBIO.COM>]

Dear Hartono,

Your second query is a coordinated subquery and gets you the latest record for 
each EMPLID.

The first example is not a coordinated subquery, as it does not refer back into 
the main query.  The subquery is evaluated once, and is interpreted as 
"A.EMPLID = A.EMPLID".  This finds one record, the one with the most recent 
date in the database.

If you write "WHERE A.EMPLID = PS_JOB.EMPLID", I think you will have the same 
thing as your second example.

Hope that helps

Dave


At 10:53 AM 4/8/1999 +0700, you wrote:
>Hi...
>Could anyone tell me what is the difference between these two Query and
>why they produce a different result ?
>I only want to get the current record from the JOB record.
>any help would very appreciated
>thanks in advance
>
>regards,
>Hartono Sutirman
>
>This one I use alias A in the SubQuery
>**************************************
>SQL> ED
>Wrote file afiedt.buf
>  1  SELECT EMPLID, EFFDT
>  2  FROM PS_JOB
>  3* WHERE EFFDT = (SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID =
>EMPLID)
>SQL> /
>
>EMPLID      EFFDT
>----------- ---------
>680000643   01-OCT-98
>
>
>This one I use alias A in the main Query
>****************************************
>SQL> ED
>Wrote file afiedt.buf
>  1  SELECT A.EMPLID, A.EFFDT
>  2  FROM PS_JOB A
>  3* WHERE A.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID =
>A.EMPLID)
>SQL> /
>
>EMPLID      EFFDT
>----------- ---------
>130344940   07-AUG-98
>680000643   01-OCT-98
>T68456789   06-APR-92
>680000136   09-AUG-98
>