Re: [sqr-users] Duplicate Row of data on flat file

[bpelton@communitymedical.org]

      AND B.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB B
            WHERE A.EMPLID = B.EMPLID AND
            B.EFFDT >= $date)

This is a poor effective date subselect.

First, I'm surprised it is working as you haven't specified which table you
are selecting effdt from, and have aliased PS_JOB as b in the subselect.

Second, PS_JOB is effective dated, effective sequenced (at least in HRMS it
is, not sure what PS Product you are using (AL tables?)

Third, more of personal interest, why would you care what their job record
looked like immediatly after their check date?  Seems you would want to
know what job record was in effect at the time of the check, which would
mean your effdt >= $date logic is incorrect.

This is what I would try:

and b.effdt = (select max(b1.effdt) from ps_job b1
where b1.emplid = b.emplid
  and b1.empl_rcd# = b.empl_rcd# (this is another key on the table,
subselects should join all keys to improve performance)
  and b1.effdt <= $date)
and b.eff_seq = (select max(b2.effseq) from ps_job b2
where b2.emplid = b.emplid
  and b2.empl_rcd# = b.empl_rcd#
  and b2.effdt = b.effdt)

This of course all applies if you are an HRMS customer, if not, still I
would look at that subselect and be sure you have joined all the keys on
the tables.

Here is a quick SQL statement to show you keys (of course you could always
look up the rec defn in app designer)

select a.fieldname,
       c.longname
  from psrecfield a,
       pskeydefn b,
       psdbfield c
 where a.fieldname = b.fieldname
   and b.fieldname = c.fieldname
   and a.recname = b.recname
   and b.recname = '&&recname'
   and b.indexid = '_'

(Replace &&recname with the record name, in this case, "JOB")

Brian Pelton
bpelton@communitymedical.org






"Kent Parker" <KParker@magnatrax.com>@sqrug.org on 10/25/2002 01:40:53 PM

Please respond to sqr-users@sqrug.org

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


To:    <sqr-users@sqrug.org>
cc:
Subject:    Re: [sqr-users] Duplicate Row of data on flat file


see attached sql select......

Thanks.

>>> GJANSEN@aflcio.org 10/25/02 02:16PM >>>
It sounds to me as if there must be a join in your query: for some reason,
those who have amount3s have more than one record in some many-to-one
table.

I'd strongly suggest you provide the begin-select paragraph. Otherwise
nobody will be able to help you.

>>> KParker@magnatrax.com 10/25/02 02:59PM >>>
I'm creating a flat file with sqr for a benefits provider & getting 2 rows
of output for each employee who has an "Amount 3" (see attached
file)....The output is correct for everyone who does not have an Amount
3.....I think I need to test to see if the employee is the same for the
current record as the previous & if so then do whatever...Just not sure how
to do it...Can anyone help with this?

Hope this makes sense..

Thanks,
Kent


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
(See attached file: sql select.txt)


Here was a non admissible message part of 'application/octet-stream'
MIME type. It has been automatically discarded before sending the
message to the list.