Sqlrpgle Multi-coalesce into data structure fails with SQLCODE -420

[OLDMAN25]

I'm trying to select into from a logical file to a data struct from this same logical file but I was getting NULL errors.

I decided to coalesce every single field but now I'm getting -420 and I'm not really sure why since the same query works in STRSQL

Dcl-ds Record Ext Extname('File1') End-ds;
Dcl-s Holder Char(10) ;

Exec-sql Set Option Commit = *None ;

Exec-sql
  Select Coalesce(Field1, ' '),
               Coalesce(Field2, ' '),
               Coalesce(Field3, 0.0),
               Coalesce(Field4, ' ')
  Into :Record
  From File1
  Where Field1 = :Holder ;

Something like above, but with way more fields. Wondering what's wrong with it and if there's a better way to handle SQLRPGLE nulls like AWLNULL.

Field3 is a packed 7:2 field. When I tested with Errind, that field is the one with null values. But on coalesce, it fails.

[OLDMAN25]

Not sure if I answered my own question here.

I did an Int(5) Array with same number of fields from File1)

**FREE                                                              
  Ctl-opt Alwnull(*Inputonly) Option(*NOdebugio:*Srcstmt:*Nounref) ;

  Dcl-ds Record Ext Extname('File1') End-ds ;                  

  Dcl-s ErrInd Int(5) Dim(18) ;                                     

  Exec sql                                                          
    Select *                                                        
      Into :Record :ErrInd                                          
      From File1
      Where Field1 = 'SomeValue' ;                                           

  *Inlr = *On ;

Program ran this sql with SQLCOD=0 and :Record populated with data along with that null field containing Zeroes already. Wonder if an Inz on the Dcl-ds is still necessary. Also is there a way to Dim an array with # of elements from a data structure?

[JonBoy]

Can't speak to your coalesce issue and don't have time to play but ....

There is a way to get your indicators if you are on 7.3 or later:

dcl-ds custData       likerec(custRec) nullind(custData_null);
dcl-ds custData_null  likerec(custRec : *null );

      read custMaster custData;

      if custData_null.lastOrder; // Customer has never ordered

Hope this helps.

[OLDMAN25]

Thanks JonBoy learning something new today. After reading abit more about it the method you shared with me is for Alwnull(*usrctl). It has a lot of control for null fields.

Never really dealt with null fields before, only now. I wanted the sql to pull all fields and set the Null values to something else, it did that when set with a null indicator via sql into syntax

[Vectorspace]

I assume from the dim(18) on your null indicator array, means the table has 18 columns. In which case, I think the problem may be you are only selecting 4 columns into an 18 field DS?

[OLDMAN25]

No I changed the fields just to mask it. But yes there are 18 fields that I used coalesce on. The null field turned out to be a packed 7:3.

I'll play around with it but I think it needs a coalesce and cast. Or just use null indicators