AW: Why is a date field not being populated by the Fetch?

Hi,

there are several things:
1. Indicator variables must be defined as 4B 0 or better 5I 0.
2. When using date fields, make sure, the date format (for SQL) is set to
either *ISO, *USA or *EUR at compile time. To set the date format you either
have to specify the date format in the compile command CRTSQLRPGI or add a
SET OPTION Statement (as 1st C-Spec) which will set the date format to your
source code.
3. If a NULL Value is returned, only the NULL-Flag (i.e. Indicator Variable)
is set, but no Value is returned, that means the previous value stays
unchanged. In RPG you need to replace manually the NULL values with a
Default values, because RPG cannot handle NULL Values in data structures or
other variables. (I doubt SQL will set the NULL flags, which are set with
native I/O if a NULL value is returned and which can be detected or set with
the RPG function %NULLIND.)
Another way is to use the scalar function COALESCE to convert a NULL value
into a Default Value for each column that must be returned and which can
hold a NULL Value.

BTW for performance issues it is never a good idea to use SELECT *, better
is only to select the columns you really need.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Taylor iSeries
Gesendet: Sunday, 06. December 2009 00:20
An: rpg400-l@xxxxxxxxxxxx
Betreff: Why is a date field not being populated by the Fetch?

Why is a date field not being populated by the Fetch?
*
We are using SQL to retrieve records into a data structure and then build a
subfile.
The pgm bombs at an Eval-Corr statement.
* 
The error message is RNQ0112, Date, Time or Timestamp value is not valid .
The date field causing the error has a Null value.  It is valid... a valid
Null.
*
Stepping through the pgm in debug mode, the date field is not changed at all
after the Fetch statement. 
*
So, after the Fetch, we set the value in the date field  & then the error
doesn't occur.
*
Here is the relevant code.. 
Files CaAgen & CaAgenJ76 are essentially the same, except the J file is a
join file with a few extra fields.
We hope someone can suggest a solution.
Thank you
*
*
     D AgRecDs       E DS                  ExtName(CAAGEN) qualified
     D AgRec         E DS                  ExtName(CAAGENJ76)
     D nullFlags       S              2b 0 dim(50)                       


 Begsr LoadSubfile;
        // Open the cursor
       If *In75;
         Exec Sql Declare cursor75 CURSOR For
              Select   *
              From     CAAGEN
              Where    AGCMDATE = :PgmCmDate
              order by AGCMDATE, AgAgType;
              For Read Only;
         Exec Sql Open  cursor75 ;
       Endif;                        
 : : : : : : : : : : : : : : : : : : : : : : : : :
 : : : : : : : : : : : : : : : : : : : : : : : : :
        If *In75;
           Exec SQL Fetch next from cursor75 Into :AgRecDs :nullFlags;
           If (SqlStt = '02000');
              *In86 = *On;
           else;
              eval-corr AgRec = AgRecDs;
           ENDIF;
        Endif; 
Endsr;