Re: SQR Error

[Ray Ontko <rayo@ONTKO.COM>]

Bill,

I'm guessing that the problem is in your where clause.
If you want to compare a date variable to a string variable,
do an explicit conversion of the string to a date.
For example:

>                         AND Effdt <= $ThruDate)

should perhaps be

                          AND effdt <= to_date($thrudate,'YYYYMMDD')

The error you're getting is Oracle complaining that the default
date format (probably 'dd-mon-yy' on your system) doesn't match
the value you're passing.

Hope this helps.

Ray
>      I am executing the following select and receiving the following sqr
>      error.  Anybody have any ideas?
>
>      Bill
>
>      (Bowersb@psph.providence.org)
>
>
>
>      (SQR 5528) ORACLE OFETCH error -1830 in cursor 2:
>         ORA-01830: date format picture ends before converting entire input
>      string
>
>      Error on line 240:
>         (SQR 3725) Bad return fetching row from database.
>
>      SQRW: Program Aborting.
>
>
>      I am using Oracle and SQR version 3.
>
>      Note: Line 240 is the begin-select statement
>
>      begin-SELECT  loops=50
>       show '100 **************************************'
>      !Personal Data
>      PD.Name
>       show '100 &pd.name: ' &pd.name
>      PD.SSN
>      PD.Address1
>      PD.Address2
>      PD.City
>      PD.State
>      PD.ZIP
>      PD.Sex
>      PD.Birthdate
>      !to_char(PD.Birthdate, 'YYYYMMDD')    &Birthdate
>      PD.Emplid
>      !Employment
>      E.Hire_Dt
>      !to_char(E.Hire_Dt, 'YYYYMMDD')       &Hire_dt
>      E.Termination_dt
>      !to_char(E.Termination_Dt, 'YYYYMMDD')    &Termination_dt
>
>      !Job
>      J.Emplid
>      J.Empl_Rcd#
>      J.Company
>      J.Empl_Status
>      J.Action_Dt
>      J.Effdt
>      J.Action
>       !show '100 &j.action: ' &J.action
>      J.Action_Reason
>       !show '100 &j.action_reason: ' &J.action_reason
>
>      !Health_benefit
>      HB.Plan_Type
>        do 1200-Write-Emp  !Builds detail record
>
>      HB.Benefit#
>      hb.coverage_elect_dt
>      hb.coverage_begin_dt
>      hb.coverage_elect
>      HB.Benefit_Plan
>      HB.Covrg_Cd
>
>      !Benefit Plan
>      bp.Plan_type
>
>
>      HB.Effdt
>      !to_char(HB.Effdt, 'YYYYMMDD')    &Effdt
>      !to_char(HB.Coverage_Begin_Dt, 'YYYYMMDD')    &covrg_begin_dt
>      !to_char(hb.Coverage_Begin_Dt, 'MM/DD/YYYY')  &Print_Coverage_Date
>
>      BP.Provider
>
>
>      FROM  PS_Personal_Data  PD,
>            PS_Employment     E,
>            PS_Job            J,
>            PS_Health_Benefit HB,
>            PS_Benef_Plan_Tbl BP
>
>      WHERE J.Effdt = (SELECT MAX(Effdt) FROM PS_Job
>                        WHERE Emplid    = J.Emplid
>                          AND Empl_Rcd# = J.Empl_Rcd#
>                         AND Effdt <= $ThruDate)
>                          !AND Effdt <= $AsOfDate
>                         !and Action_dt between $FromDate and $ThruDate)
>                          !and Action_dt >= $FromDate)
>
>        AND J.Effseq = (Select MAX(Effseq) FROM PS_Job
>                         WHERE Emplid    = J.Emplid
>                           AND Empl_Rcd# = J.Empl_Rcd#
>                           AND Effdt     = J.Effdt)
>        AND J.Empl_Rcd# = 0
>        AND J.Company in [$Company_Reqstd]
>        AND E.Emplid = J.Emplid
>        AND E.Empl_Rcd# = 0
>        AND PD.EmplID = J.EmplID
>        AND HB.EmplID = J.EmplID
>        AND HB.Empl_Rcd# = J.Empl_Rcd#
>        AND HB.Benefit# = 0
>       AND HB.Effdt = (SELECT Max(Effdt)
>                        FROM PS_Health_Benefit
>                       WHERE Emplid    = HB.Emplid
>                         AND Empl_Rcd# = HB.Empl_Rcd#
>                         AND Plan_Type = HB.Plan_Type
>                         AND Benefit#  = HB.Benefit#
>                         !and coverage_begin_dt is not null
>                         and coverage_begin_dt < = $ThruDate)
>                         !and effdt < = $ThruDate)
>
>         AND HB.Coverage_Elect = 'E'
>        AND (HB.Coverage_End_Dt IS NULL  OR
>             HB.Coverage_End_Dt > $ThruDate)
>        AND BP.Plan_Type    = HB.Plan_Type
>        AND BP.Benefit_Plan = HB.Benefit_Plan
>        AND BP.EffDt = (SELECT Max(EffDt)
>                         FROM PS_Benef_Plan_Tbl
>                        WHERE Plan_Type    = BP.Plan_Type
>                          AND Benefit_Plan = BP.Benefit_Plan
>                          and effdt < = $ThruDate)
>
>        AND BP.Provider = 'FRSTHE'
>
>      ORDER BY PD.Name, HB.Plan_Type,HB.Benefit_Plan
>
>      end-SELECT
>
>      end-procedure
>

----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "Time for a new signature line."
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/