Re: More SQL fun...selective update -- MIDRANGE-L

You could use a CASE structure in your SQL, something like this whereveryou have the need to use the date


update mksrcw
set d2fodt = (select
case eve1dt = 0
  then '1900-01-01'
else
   substr(char(eye1dt + 19000000),1,4) || '-' ||
   substr(char(eye1dt + 19000000),5,2) || '-' ||
   substr(char(eye1dt + 19000000),7,2)
end
from mssrc where mksrcw.d2comp = eycomp
and mksrcw.d2srcd = eysjcd
and eye1dt > 0)

or use the case ... then ... else ... end construct in the SQL thatgenerates your output file - if it does.

At 03:01 PM 9/13/2005, you wrote:

Our third party order entry system is written in Synon/Cool2E/Whatever,
and produces all its date fields in a 7 digit format, CYYMMDD.  The C is
actually a 0 for pre2K dates, a 1 for >=2K dates, and presumably will be
a 2 if we make it to the year 2100.  So today, for example is 1050912.

Since the century is essentially the real century minus 19, I've taken
to adding 19000000 to these dates to convert them to CCYYMMDD, then
throwing in some separators to make them ISO.  So 1050912 + 19000000 =
'2005-09-12'.

The trouble comes when I try to use this routine in SQL.  If any of the
fields have a date of zero, then my little equation produces an invalid
date (1900-00-00), and I can't insert it into a date formatted field.

So instead, I thought I would produce my output file with all the dates
hardcoded to '0001-01-01', then use an update statement to fetch only
the valid dates and update them, like so:

update mksrcw
set d2fodt = (select
substr(char(EYE1DT + 19000000),1,4) || '-' ||
substr(char(EYE1DT + 19000000),5,2) || '-' ||
substr(char(EYE1DT + 19000000),7,2)
from mssrc where mksrcw.d2comp = eycomp
and mksrcw.d2srcd = eysjcd
and eye1dt > 0)

I figured that last line would prevent any attempt to update the field
if the date were zero, and my '0001-01-01' would just remain in the
field, which would be nice.  However, it apparently tries to update the
field with a NULL value, having not found a record matching all the
criteria.

Is there a way to do this?

Thanks

Greg Fleming


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.