Re: Issue with $AsOfDate on DB2 (works on SQL Server)

["Turner, Ivan" <Ivan.Turner@QWEST.COM>]

If To_Number($One_Dt) > To_Number($Two_Dt)

and to_number(VNDRLOC_EFFDT.EFFDT) <=  to_number($AsOfToday))

-----Original Message-----
From: Heely, Paul [mailto:PHeely@AMESHOME.COM]
Sent: Friday, January 18, 2002 3:05 PM
To: SQR-USERS@list.iex.net
Subject: Re: Issue with $AsOfDate on DB2 (works on SQL Server)


Are you explicitly declaring $AsOfDate to be a date?  I found that I had to
do this when I was trying to match effdt to our fiscal month dates.

declare-variable
        date $var_name
end-declare

Hope it helps.

--Paul

> -----Original Message-----
> From: Kevin Jarvis [mailto:jarviske@YAHOO.COM]
> Sent: Friday, January 18, 2002 2:28 PM
> To: SQR-USERS@list.iex.net
> Subject: Issue with $AsOfDate on DB2 (works on SQL Server)
>
>
> I'm having an issue where it seems to not be able to
> compare $AsOfDate to EFFDT on DB2.  I've been looking
> all over for an answer as to why this is happening,
> but haven't had any luck.  Thanks for the help!!
>
> For the following code:
>
> begin-procedure Get-Vendor-Loc-Addr
> #debugh do debug-msg('Get-Vendor-Address')
>
> begin-select
> VNDRLOC.ADDR_SEQ_NUM_ORDR
> from
>   PS_VENDOR_LOC VNDRLOC
> where
>   VNDRLOC.SETID         = &AUC_DISP_HDR.BIDDER_SETID
> AND
>   VNDRLOC.VENDOR_ID     = &AUC_DISP_HDR.BIDDER_ID AND
>   VNDRLOC.VNDR_LOC      = &AUC_DISP_HDR.BIDDER_LOC AND
>   VNDRLOC.EFFDT         =
>     (SELECT MAX(EFFDT)
>     from
>       PS_VENDOR_LOC VNDRLOC_EFFDT
>     where
>       VNDRLOC_EFFDT.SETID = VNDRLOC.SETID and
>       VNDRLOC_EFFDT.VENDOR_ID = VNDRLOC.VENDOR_ID and
>       VNDRLOC_EFFDT.VNDR_LOC = VNDRLOC.VNDR_LOC and
>       VNDRLOC_EFFDT.EFFDT <=  $AsOfToday) AND
>   VNDRLOC.EFF_STATUS    = 'A'
> end-select
>
> End-Procedure !Get-Vendor-Loc-Addr
>
> I receive this error:
> (SQR 5528) DB2 SQL OPEN/EXECUTE error -302 in cursor
> 23:
>    SQL0302N  The value of a host variable in the
> EXECUTE or OPEN statement is too
>
> large for its corresponding use.  SQLSTATE=22001
>
> Error on line 541:
>    (SQR 3723) Problem executing cursor.
>
> SQR for PeopleSoft: Program Aborting.
>
> __________________________________________________
> Do You Yahoo!?
> Send FREE video emails in Yahoo! Mail!
> http://promo.yahoo.com/videomail/
>