Re: put on 7.4 TR4, now getting a SQL error

[Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>]

this sql statement had a bad COALESCE, in that it was a numeric field, but
I was replacing it with a blank...


7.4 memo to users has been updated:

  Queries that compare a numeric value with blanks or an empty string will
fail with SQLSTATE/SQLCODE ‘22023’/-302 or '53045'/-678 Previous to PTF
SI76030 on IBM i 7.4 and PTF SI76035 on IBM i 7.3, a query that included an
invalid comparison between a numeric value and blank(s) or a numeric value
and an empty string would not result in an SQL failure. Blank characters
and empty strings are both incompatible with numeric values. Any such
queries will now fail with SQLSTATE: '22023'/-302 or '53045'/-678.

On Sun, May 30, 2021 at 12:52 PM Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
wrote:

> other apps showing this as well.
> when we run the SQL statements in STRSQL,  they work normally.?
>
> I have Put in a case with IBM.
>
> On Sun, May 30, 2021 at 11:17 AM Gerald Magnuson <
> gmagqcy.midrange@xxxxxxxxx> wrote:
>
> > I don't know what "item 2"  is describing???:
> > here is message (sql to follow):
> >
> > Message ID . . . . . . :   CPD4363
> >
> > Date sent  . . . . . . :   05/30/21      Time sent  . . . . . . :
> > 11:11:44
> >
> >
> > Message . . . . :   Literal value for item 2 not valid. Reason code 1.
> >
> >
> >
> > Cause . . . . . :   A value that is not valid was specified for item
> > number 2
> >   in specification type 3.  The literal value is not valid for reason
> > code 1.
> >   The reason codes and their meanings follow:
> >
> >      1 -- The value is not numeric or is numeric but is too large for the
> >
> >   receiver.
> >
> >      2 -- A numeric value contains greater than 63 total digits.
> >
> >      3 -- A floating-point value has a significand that is not valid.
> >
> >      4 -- A floating-point value has too many exponent digits.
> >
> >      5 -- A floating-point value has an exponent that is not valid.
> >
> >      6 -- A floating-point special value that was not valid was
> > specified.
> >      8 -- Floating-point overflow.
> >
> >
> >  More...
> > Press Enter to continue.
> >
> > SQL:
> >
> > exec sql Declare C1 cursor for
> >
> >  Select DISTINCT COALESCE(MNUUSRHOT,0) as MNUOPTID, MNUAPPID,
> >
> >        MNUJOB, MNUONM, MNUCMD, MNUADT, COALESCE(MNUUSRESC,' ' )
> >
> >         as MNUUSRESC,  COALESCE(MNUUSRSCH,' ' ) as MNUUSRSCH
> >
> >  from MENUOPT
> >
> >  left join MENUUSROPT  on MNUAPPID = MNUUSRAID and :user1 <> '*ALL'
> >
> >  where  (
> >
> >          MNUUSRPRF = :user1  or :user1 = '*ALL'
> >
> >          )
> >
> >       and    (
> >
> >           UPPER(MNUONM)  like '%' concat trim(:sfl_search) concat '%'
> >
> >         or
> >
> >           UPPER(MNUCMD) like '%' concat trim(:sfl_search) concat '%'
> >
> >         or
> >
> >         UPPER(MNUUSRSCH) like '%' concat trim(:sfl_search) concat '%'
> >
> >         or
> >
> >           trim(:sfl_search) = ''
> >
> >               )
> >
> >  order by  MNUAPPID ;