Re: Next SQL question

["Peter Dow" <pcdow@xxxxxxxxx>]

Hi Tim,

I seem to remember having this problem and it had to do with null values,
i.e. the date field was null so it showed the default value of 0001-01-01,
but it's actually null, which SQL won't touch. Try the COALESCE function,
e.g.

    SELECT COALESCE(datefield,'0001-01-01'),fld2,fld3 FROM filename

The COALESCe function returns the first value in its list that is non-null.
In the example, if datefield is null, you'll get 0001-01-01 as the non-null
value for the first column of your SELECT's result set.

hth,
Peter Dow
Dow Software Services, Inc.
909 425-0194 voice
909 425-0196 fax


----- Original Message -----
From: "Hatzenbeler, Tim" <thatzenbeler@clinitech.net>
To: <rpg400-l@midrange.com>
Sent: Tuesday, August 21, 2001 2:09 PM
Subject: Next SQL question


> This message is in MIME format. Since your mail reader does not understand
> this format, some or all of this message may not be legible.
> --
> [ Picked text/plain from multipart/alternative ]
> My embedded sql/rpg program works, sorta, it works only if my date fields
> have real values in them, and not the default 0001-01-01 values...
>
> what should I do?  I keep getting a 183 error, which is a timestamp
problem,
> but If I force in a date, it does work...  But I was hoping to use *loval
to
> inform me that my date field is unchanged...
>
> thanks, tim
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com