RE: SQL insert - all fields

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Jeff,

Not bad.

A couple of improvements.  You don't really need this:
        Declare InvalidDate Condition For '22007' ;
        Declare Continue HANDLER for InvalidDate
                Set CvtDate = 00010101;

Since your input is a date data type, by definition it _CAN'T_ be invalid.

The exception is if it is NULL.  But you handle that case by saying:
        Returns NULL on NULL input

If you were going the opposite way, from numeric to date, then would want the 
error handling above.


Also, as you see from my example, you don't need a temporary variable.  You can 
in SQL just like RPGLE, return the results of a expression directly.

I'll let you look up the rest in the SQL manual.  In particular, find out what 
the DETERMINISTIC does, it can be important for performance.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeff Crosby
> Sent: Thursday, June 16, 2005 3:50 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL insert - all fields
> 
> 
> > > Do yourself a favor and create a UDF to handle the conversion.
> > 
> > Oh crap.  Something else to learn. <vbg>
> > 
> > Thanks, I'll figure it out.
> 
> Woohoo!  I got it!
> 
> How does this look:
> 
> Create Function Dilgard/ISOToDec
>         (ISODate Date )
>         Returns Decimal(8,0)
>         Language SQL
>         Deterministic
>         Contains SQL
>         Returns NULL on NULL Input
>         No External Action
>         Set Option DbgView = *Source,
>                    DatFmt = *ISO
> BEGIN
>         Declare CvtDate DEC(8,0);
>         Declare InvalidDate Condition For '22007' ;
>         Declare Continue HANDLER for InvalidDate
>         Set CvtDate = 00010101;
>         Set CvtDate = Dec(Substr(Char(ISODate,ISO),1,4) Concat
>                           Substr(Char(ISODate,ISO),6,2) Concat
>                           Substr(Char(ISODate,ISO),9,2));
> 
>         Return CvtDate;
> END; 
> 
> I don't know what some of it means just yet, but it works!
> 
> -- 
> Jeff Crosby
> Dilgard Frozen Foods, Inc.
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
> 
> The opinions expressed are my own and not necessarily the 
> opinion of my
> company.  Unless I say so.
> 
> 
> 
> -- 
> 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.
> 
>