Re: SQLBase & SQR Help

[Scott Gunter <ksgunter@TDYRYAN.COM>]

I want to thank everyone for there help.

I was finally able to select records from a table based on whether the column
"termination_dt" has a year value equal to $currentyear. The key was that
you can use SQLBase functions (described in the SQLBase SQL Reference
Manual) in the Where clause of a Begin-Select.

The following Select statement is a trimmed down version of what I finally
used.

begin-SELECT
A.emplid
B.deptid
A.orig_hire_dt
A.name
B.action
B.action_reason
B.action_dt
B.empl_status
A.ssn
B.File_nbr
C.termination_dt
FROM PS_Personal_Data A, PS_Job B, PS_Employment C
  WHERE A.emplid = B.emplid
    AND C.emplid = B.emplid
    AND C.empl_rcd_nbr = B.empl_rcd_nbr
    AND B.paygroup = '&XQ'
    AND (B.empl_status in ('A','L','P')
         OR (B.empl_status in ('D','R','T') AND
             @yearno(C.termination_dt) = $currentyear)
         OR (B.empl_status = 'S' AND
             @yearno(B.effdt) = $currentyear))
    AND B.Effdt = (SELECT MAX(Effdt)
         FROM PS_Job B1
         WHERE B1.Emplid = B.Emplid AND
               B1.Empl_Rcd_Nbr= B.Empl_Rcd_Nbr AND
               B1.Effdt <=SYSDATE)
    AND B.EffSeq = (SELECT MAX(EffSeq)
         FROM PS_JOB B2
         WHERE B2.Emplid = B.Emplid AND
               B2.Empl_Rcd_Nbr= B.Empl_Rcd_Nbr AND
               B2.Effdt = B.Effdt)
    ORDER BY A.emplid
end-SELECT



Scott Gunter
IS Representative SR
Teledyne Ryan Aeronautical
619/291-7311 x1741