Re: Simple Query

[Michael Lee <MLee@AGCONSULT.COM>]

Hartono.

If you want to obtain the "CURRENT" JOB record, then your SQL is incorrect.

Here's a query that should work, substituting [current date] with your DB's
alias to the current date:

SELECT A.EMPLID, A.EFFDT
FROM PS_JOB A
WHERE A.EFFDT = (SELECT MAX(B.EFFDT)
                 FROM PS_JOB B
                 WHERE B.EMPLID    = A.EMPLID
                   AND B.EMPL_RCD# = A.EMPL_RCD#
                   AND B.EFFDT    <= [current date]);

You need to be aware of future dated rows in PeopleSoft.  PeopleSoft is an
effective-dated system and future dated rows are legal.

Hope this helps.  Good luck.


Michael K. Lee
mlee@agconsult.com
Consultant
AG Consulting, an ADP Company
http://www.agconsult.com






Hartono Sutirman <hsutirman@JATIS.COM> on 04/07/99 08:53:02 PM

Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Michael Lee/Application Group)
Subject:  Simple Query




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