RE: [sqr-users] Very SQR Beginner

[bpelton@communitymedical.org]

First, what you have is not a valid SQL paragraph.

Second, you need to join all key fields in the subselect minus the
effective date

And third, if you do not use effective sequence logic, you could return
multiple rows for one employee.

The most simple effdt/effseq logic is below:

select
  a.emplid
  a.empl_rcd#
 from ps_job a
where a.effdt      = (select max(a1.effdt) from ps_job a1
                       where a1.emplid         = a.emplid
                         and a1.empl_rcd#      = a.empl_rcd#
                         and a1.effdt         <= sysdate)
  and a.effseq     = (select max(a2.effseq) from ps_job a2
                       where a2.emplid         = a.emplid
                         and a2.empl_rcd#      = a.empl_rcd#
                         and a2.effdt          = a.effdt)


Brian Pelton
System Analyst
Community Medical Centers






"Mai To" <mai.to@uth.tmc.edu>@sqrug.org on 12/04/2002 12:27:05 PM

Please respond to sqr-users@sqrug.org

Sent by:    sqr-users-admin@sqrug.org


To:    <sqr-users@sqrug.org>
cc:    <LRoux@syr.edu>
Subject:    RE: [sqr-users] Very SQR Beginner



Why can't we use something simple like this:

AND B.EFFDT
       FROM PS_JOB B1
       WHERE B.EFFDT  <= $SysDate)
  AND C.EFFDT
 FROM PS_JOBCODE_TBL C1
 WHERE C.EFFDT <= $SysDate)

Many Thanks.

Mia


>>> This is to use Effective Dating.  PeopleSoft uses Effective Dating
to maintain history.  Using Effective Dating/ Effective Sequencing and
Status you can specify at any time (even up front) which rows of data
are effective at a given time.  For instance, say you know right now
that you will live somewhere from 1/10/2002 to 5/5/2003 then somewhere
else between 5/6/2003 and forward.  In Peoplesoft that is done by
Effective Dating.  The rows of data would look like:

EMPLID  EFFDT     ADDRESS
1234567  1/10/2002 120 Some Address
1234567  5/6/2003   1547 some other address

The code above finds the row that is most effective (that is, less than
or equal to today's date and not in the future).  As of today that row
would be the first one.  As of 5/6/2003 the second address would be in
effect and the code would select that one.



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users




_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users