sponsored links



No announcement yet.

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

  • Filter
  • Time
  • Show
Clear All
new posts

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

    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 ;
      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.

  • #2
    Not sure if I answered my own question here.

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

      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?


    • #3
      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.


      • #4
        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


        • #5
          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?


          • #6
            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