RE: Embedded SQL - fetching a date

["Kurt Anderson" <kjanderson@xxxxxxxxxxxxx>]

Sorry, sent an incorrect file layout.  Corrected: 

Data on FILEA:
A            OHNUM          7P 0
A            OHBILL         9P 0
A            OHKEYDATE       L 

Kurt Anderson
Application Developer
Highsmith Inc

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kurt Anderson
Sent: Friday, July 15, 2005 2:11 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Embedded SQL - fetching a date

Here's the pertinent code.
The date does not allow a NULL value.
Precompiler recognizes the date format as *ISO.  Date stored on file is
*ISO.  Programs default date format to *ISO.

Thanks,
Kurt


Date on FILEA:
A            DATE            L 

--- D Specs
 //  File Templates
D FILEA_Tmpl  E DS                    ExtName( FILEA ) Based(@) 
D                                     Prefix( OH_ )              
                                                                 
 //  Data structure to hold Order Header records read by SQL     
D FILEADS         DS                  Occurs(50)                 
D  OrdNum                             Like( OH_OHNum )           
D  BillTo                             Like( OH_Bill )            
D  KeyD                               Like( OH_OHKeyDate )        

--- Declare
C/EXEC SQL                          
C+ DECLARE DATA1 CURSOR FOR        
C+ SELECT OHNUM, OHBILL, OHKEYDATE  
C+   FROM FILEA                     
C+  WHERE OHKEYDATE >= '2005-01-01' 
C+   ORDER BY OHBILL                
C/END-EXEC                          

--- Fetch
C/EXEC SQL                                       
C+ FETCH FROM DATA1 FOR 50 ROWS INTO :FILEADS   
C/END-EXEC                                       

Kurt Anderson
Application Developer
Highsmith Inc

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tyler, Matt
Sent: Friday, July 15, 2005 1:55 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Embedded SQL - fetching a date

Can you show us the code being this problem? I have never had an issue
getting date data, except for two reasons.  One, the date format is to
short for dates outside IBM's window for short dates like *MDY.  Two,
the result field data was NULL.  In the latter case you also have to
specify the null indicator field, structure.  

Thank you,
Matt Tyler
WinCo Foods, LLC
mattt@xxxxxxxxxxxxxx


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Kurt Anderson
Sent: Friday, July 15, 2005 10:50 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Embedded SQL - fetching a date

Our default is *ISO.
Added in the Set statement to change it to *ISO explicitly, but the
program still doesn't like putting the date into the Date data structure
subfield.

The subfield is defined off the same field I'm retrieving the date from,
so there shouldn't be any format discrepancies there.  


Kurt Anderson
Application Developer
Highsmith Inc

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tyler, Matt
Sent: Friday, July 15, 2005 10:30 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Embedded SQL - fetching a date

Did you create the program or set the option like this?

C/EXEC SQL                 
C+ SET OPTION DATFMT = *USA
C/END-EXEC                 

Or

C/EXEC SQL                 
C+ SET OPTION DATFMT = *ISO
C/END-EXEC                 

The default is *MDY. 

Thank you,
Matt Tyler
WinCo Foods, LLC
mattt@xxxxxxxxxxxxxx


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Kurt Anderson
Sent: Friday, July 15, 2005 9:23 AM
To: RPG programming on the AS400 / iSeries
Subject: Embedded SQL - fetching a date

I can't seem to fetch a date format field and place it in a date format
field in a Data Structure.  If I change the field in the DS to be 10A
instead of D, then it works.
 
Is there something I can do so I can drop the value right into a date
format field instead of having to drop it into an Alpha field and then
move it to a date format field myself?
 
I don't seem to have this problem when I do a simple Select Into
statement.
 
Thanks,
 
Kurt Anderson
Application Developer
Highsmith Inc.
W5527 State Road 106, P.O. Box 800
Fort Atkinson, WI 53538-0800
TEL (920) 563-9571  FAX (920) 563-7395
EMAIL kjanderson@xxxxxxxxxxxxx