Re: SQLBase & SQR Help -Reply

[Wes Williams <wwilliams@MAIL.RKD.SNDS.COM>]

Scott try the following:
  @yearno(C.termination_dt) = $currentyear
Wes

>>> Scott Gunter <ksgunter%TDYRYAN.COM@internet.rkd.snds.com>
01/08/98 05:39pm >>>
Jeremy,

Thanks for the help. I don't know if there is a table in SQLBase like the
one you describe in Oracle. However the following worked for me:

  date-time () YYYY &curryear  !Get current year from SQLBase
  let $currentyear = &curryear

I placed this code in my Init-Report procedure (not within a begin-select).
It returns the current year from SQLBase.

Now I am trying to select records from a table based on whether the
column
"termination_dt" has a year value equal to $currentyear. I tried using
the "substr" function on the termination_dt (i.e.
substr(C.termination_dt,7,4)
= $currentyear) but this only causes a "missing right parentheses"
error).

If you have any suggestions I'm all ears.
Here is the (trimmed down version) Select statement:

begin-SELECT
A.emplid
A.orig_hire_dt
A.name
B.empl_status
A.ssn
B.File_nbr
C.termination_dt
  add  1 to #TotalRecords
  Do WriteRecordOut
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.empl_status in ('A','L','P')
       OR (B.empl_status in ('D','R','T') AND
           substr(C.termination_dt,7,4) = $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

Thanks again.

Scott Gunter
ksgunter@tdyryan.com
Teledyne Ryan Aeronautical
619/291-7311 x1741


At 09:34 AM 1/8/98 -0600, you wrote:
>At 04:31 PM 1/7/98 -0800, you wrote:
>
>>I need to know how to access the current date in an
>>SQR Batch program that is connecting to a SQLBase
>>database.
>
>You can just write a select statement to give
>you the date right from SQLBase. I'm not familiar
>with SQLBase per se, but there is probably a table
>with one row, or a dummy table (like DUAL in Oracle).
>You can right a select in a procedure like:
>
>begin-select
>SYSDATE &Date
>  move &Date to $Date
>from DUAL
>end-select
>
>Hope this helps,
>Jeremy
>
>
>---
>Jeremy C. Radwan  <jradwan@concentric.net>  -
>http://www.concentric.net/~jradwan/
>"Although it is not exactly 'real,' 'cyberspace' is a genuine place. Things
>happen
> there that have genuine consequences."  - Bruce Sterling, "The Hacker
>Crackdown"
>
>
Scott Gunter
IS Representative SR
Teledyne Ryan Aeronautical
619/291-7311 x1741