RE: SQL insert - all fields

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

We'll try that again....it was just a text post...don't know what happened..

create function cwlibr/CvtDateToNum (inDate date)       
    returns decimal(8,0)                                
language SQL                                            
not fenced                                              
deterministic no external action                        
returns null on null input                              
contains SQL                                            
begin                                                   
  return dec(substr(char(inDate,ISO),1,4)               
                    concat substr(char(inDate,ISO),6,2) 
                    concat substr(char(inDate,ISO),9,2) 
             ,8,0);                                     
end                                                     


To use:

INSERT INTO OLITMMST (SELECT A.*, CvtDateToNum(CURRENT_DATE)
                      FROM DMITMMST A 
                      WHERE ACREC ='D');


See Jeff, UDFs are pretty simple and quite handy!

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 Wilt, Charles
> Sent: Thursday, June 16, 2005 3:41 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL insert - all fields
> 
> 
> Jeff,
> 
> You owe me a beer...
> 
> create function CvtDateToNum (inDate date)       
>     returns decimal(8,0)                                
> language SQL                                            
> not fenced                                              
> deterministic no external action                        
> returns null on null input                              
> contains SQL                                            
>