RE: More SQL fun...selective update

I use this often and found it useful to put the code into an SQL
function. Your sql code looks a little easier to read.....my function
returns a date, so would need to convert to char......

update mksrcw
set d2fodt = (select
char(synondate(eye1dt))
from mssrc where mksrcw.d2comp = eycomp
and mksrcw.d2srcd = eysjcd
and eye1dt > 0)


SET PATH "QSYS","QSYS2" ; 


CREATE FUNCTION <YOURLIB>/SYNONDATE ( 
        DECDATE DECIMAL(7, 0) ) 
        RETURNS DATE   
        LANGUAGE SQL 
        SPECIFIC <YOURLIB>/SYNONDATE 
        DETERMINISTIC 
        READS SQL DATA 
        RETURNS NULL ON NULL INPUT 
        NO EXTERNAL ACTION 
        NOT FENCED 
        BEGIN ATOMIC 
                DECLARE FOO DATE ; 
                CASE WHEN DECDATE = 0 THEN SELECT DATE ( '1900-01-01' )
INTO FOO FROM SYSIBM / SYSDUMMY1
; 
                ELSE 
                SELECT DATE ( SUBSTR ( CHAR ( DECDATE + 19000000 ) , 1 ,
4 ) || '-' || 
                SUBSTR ( CHAR ( DECDATE + 19000000 ) , 5 , 2 ) || '-' ||

                SUBSTR ( CHAR ( DECDATE + 19000000 ) , 7 , 2 ) 
                        ) INTO FOO 
                FROM SYSIBM / SYSDUMMY1 ; 
                END CASE ; 
                RETURN FOO ; 
                END  ; 

COMMENT ON SPECIFIC FUNCTION <YOURLIB>/SYNONDATE 
        IS 'format synon date to sql date' ; 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Wednesday, 14 September 2005 1:16 PM
To: Midrange Systems Technical Discussion
Subject: Re: More SQL fun...selective update

You could use a CASE structure in your SQL, something like this wherever
you 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 that
generates 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.

--
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.

**********************************************************************
This message is intended for the addressee named and may contain
privileged information or confidential information or both. If you
are not the intended recipient please delete it and notify the sender.
**********************************************************************