RPGLE Embedded SQL Not null

1380 pts.
Tags:
AS/400
Embedded SQL
RPGLE
SQL
RPGLE Embedded SQL is getting a SQL0305 (SQLcid-305, sqlstate 22002) when fetching the next record from a view.  This is pointing to null values encountered.

 /free                                                   exec sql                                                fetch next from C1 into :wtclaims                  ;  /end-free                                         


Wtclaims is an Externally Described Data structure.

There could be null values in any field in the ds over the files.

What are mt options to handle null values?

Ihave noted code where 'not null' is used.  Do I have to do this for each field?  Can something be done for the whole DS or as a compile option?

Thanks,

Nick   



Software/Hardware used:
AS400 RPGLE SQL

Answer Wiki

Thanks. We'll let you know when a new response is added.

see this article <a href=”http://www.itjungle.com/fhg/fhg102908-story02.html”>Meaningful Names for Null Indicators</a>

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • NickHutcheson1
    *RNF0342 20 2 The parameter for %NULLIND is not valid; %NULLIND is ignored.
    H ALWNULL(*UsrCtl)   
    **-Note: WTClaims is an SQL view Left Outer Join over the header and detail.
    D wtclaims      E DS                  EXTNAME(wtclaims) inz 
    D NullInds        s              5i 0 dim(67)               
    D NullValue       s                   like(NullInds) inz(-1)
    
    C                   DOU       sqlcod <> 0                
     * Get a record                                          
    C                   exsr      $fetchnext                 
     * Eof? Eo Page?                                         
    C                   if        sqlcod = 0 and RRN < sflpag
    C                   exsr      $nullchk                   
    C                   EVAL      RRN = RRN + 1 
    C                   WRITE     sflrcd        
    C                   EndIf    
    C                   ENDDO    
    
    C     $FetchNext    Begsr                                  
     /free                                                     
      exec sql                                                 
          fetch next from C1 into :wtclaims :NullInds        ;    
     /end-free                                                 
    C                   EndSr                                
    
      C     $nullchk      Begsr                   
     /free                                      
      if NullInds(2)    = NullValue;            
                 Eval %NullInd(WHCDAT)=*Off;    
                 Eval WHCDAT  = d'1944-01-01';  
             endif;                             
    1,380 pointsBadges:
    report
  • TomLiotta
    Can you show us the database definition of WHCDAT? Tom
    125,585 pointsBadges:
    report
  • BigKat
    I believe WHCDAT itself is not a null capable field (IN THE PROGRAM) because it is defined by the external data structure and is used in the SQL fetch. I don't remember if it is populated with "garbage", blanks, or still has a prior value, but when you check the NullInds(2) field, if it is nullvalue, you ignore the contents, otherwise you use them. In your case, you set it to a default value (1/1/44). if you use SQL to update the field, you would then need to set NullInds(2) = 0.
    8,330 pointsBadges:
    report
  • TomLiotta
    if you use SQL to update the field, you would then need to set NullInds(2) = 0. If it's from a LEFT OUTER JOIN, the UPDATE probably won't be useful. I believe WHCDAT itself is not a null capable field (IN THE PROGRAM) ..., but when you check the NullInds(2) field... If NullInds(2) is the expected null indicator element, then we're probably looking at a column from the left-side of the join. If WHCDAT is not null-capable in the database, then I'm not sure if NullInds(2) will relate to WHCDAT. Each element of the array should match a null-capable field from the record format not just the next field in the format. If WHCDAT is the second field and is not null-capable, then NullInds(2) ought to match up with the third field in the format, or whichever the next null-capable field is. A field that isn't null-capable shouldn't have a null indicator. But -- I don't have an example to test. That's just a thought from how things would make sense to me. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    I don’t have an example to test. I still don't have an example, but I tried a trivial test that helped only slightly. I had a small SQLRPGLE that did a straight cursor/FETCH into a DS with a null array specified. I simply submitted it for a compile with CRTSQLRPGI ... TOSRCFILE(MYLIB/QSQLTEMP1) specified. That put the pre-compiler source output into a source member that I could look at. The record format had six columns, two of those are null-capable. The null-indicator array was defined as DIM(4). The generated source showed that four values were being moved into the array. So, it's either moving a value for each field whether null-capable or not or it sets null-indicator elements simply for however many the DIM() says exist and the rest receive some default (hopefully the 'not null' value) or undefined value. In the first case, the value set for a non-null-capable field is still unknown. In the second case, the two null-capable columns would be in the first two elements and the last two elements should be ignored. The User Controlled Support for Null-Capable Fields and Key Fields topic in the V5R4 RPG Reference has stuff to say about the OP, but it looks as if everything should work. A LEFT OUTER JOIN should include null-capable columns and the wtclaims DS is defined over the external file description. Apparently that makes every subfield in the DS to be null-capable... which seems risky. This DS won't be used for output by Nick's program, so risk is minimal. But what happens when any program uses a DS for output and one of the subfields is null-capable? The manual seems to say that all subfields become null-capable in the program. That would seem to open a risk of data-mapping errors. Looks like more testing is called for. Tom
    125,585 pointsBadges:
    report
  • BigKat
    The field in the PROGRAM is not null capable hence the %nullind doesn't work. The field in the QUERY is null capable and therefore the nullind(i) array entry must be checked.
    8,330 pointsBadges:
    report
  • NickHutcheson1
    The issue was the compile message of, *RNF0342 20 2 The parameter for %NULLIND is not valid; %NULLIND is ignored. I did not understand why %nullind was not valid when the document from BigKat and another called, The Null Nemesis said it should be. Here is what I've done. Changed the sql view to CREATE VIEW WTCLAIMS AS SELECT * FROM WTCHDR JOIN WTCDET ON WTCHDR.WHCNUM = WTCDET.WDCNUM . Removed the $nullchk routine completely. Changed the loop logic to not load the record to the subfile if the previous record has the same WHCNUM (claim number). They only want one record for each claim on the subfile no matter how many detail lines are associated with the claim number. It seems to be working without any checks or changes of null values. Now, Yes- the WHCDAT is a header or left field of the view. It is defined in DDS as data type L (date data type). The sql view is over this header file (left) and a detail file (right) and is called WTCLAIMS. This is also the name of the E DS (no source) used to pull in all fields from both files in the sql fetching. The DS is not used for output. Regular RPG updates and writes to each individual file. (K.I.S.S.) I am not sure why this worked, but I'm glad it did so I would not have to check/load each individual field in the view(67 of them). I will update this post if any other 'quirks' are discovered. Thanks to all. Nick
    1,380 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following