RE: More SQL fun...selective update

Yes use COALESCE (your new value, default value)

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Fleming, Greg (ED)
Sent: Tuesday, September 13, 2005 1:02 PM
To: midrange-L@xxxxxxxxxxxx
Subject: More SQL fun...selective update

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