Re: Simple Query

[Michael Lee <MLee@AGCONSULT.COM>]

Lisa,
Thanks for the correction.  8-)  EFFSEQ is defintely one that you need to
obtain the max of...

I responded pretty late in the evening.

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





Lisa Prokopeak <Lisa.Prokopeak@EY.COM> on 04/08/99 06:00:55 AM

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:  Re: Simple Query




Make sure to add the EFFSEQ also, as a person can have multiple rows for
the same effdt.

select j.emplid, j.effdt
from ps_job j
and    j.effdt   = (select max(effdt)
                    from   ps_job
                    where  emplid    = j.emplid
                    and    empl_rcd# = j.empl_rcd#
                        and    effdt    <= [current date])
and    j.effseq  = (select max(effseq)
                    from   ps_job
                    where  emplid    = j.emplid
                    and    empl_rcd# = j.empl_rcd#
                    and    effdt     = j.effdt);

-Lisa




MLee@AGCONSULT.COM on 04/08/99 02:40:45 AM
Please respond to SQR-USERS@list.iex.net@Internet
To:     SQR-USERS@list.iex.net@Internet
cc:
Subject:        Re: Simple Query

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