Cobol error db2 nulls values

Question

Yesterday at work I ran a program by another and I returned an error on a cursor. The cursor was a join between two tables and any fields in the fetch or where sentece doesn't contains null values. The fetch sentence contained three fields. And the tables contained 25 fields everyone, some nulls.

The error was sqlcode= -305 (null values).

Does anyone know why should not use null fields returns me that error ?

For example,

In the cursor:

SELECT A.FIELD1, B.FIELD2, B.FIELD3
FROM TABLE1 AS A LEFT JOIN TABLE2 AS B ON A.FIELD4 = B.FIELD5

The fields (FIELD1....FIELD5) not nulls, but in the tables, for example field16 is null.

In the fetch:

EXEC SQL
  FETCH MY-CURSOR INTO :FIELD1-A, :FIELD2-B, :FIELD3-B
END-EXEC

The field1, field2 and field3 not null,Indicators are needed ?

The exactly code:

 EXEC SQL                                              
   DECLARE CTOAZ7OA CURSOR WITH HOLD FOR                
     select B.C00LTIPO_ADQ, B.C00LCSB_ADQ, A.COAZ7CRESU
     from TOAZ7S1 A left outer join toaZFs1 B on        
       (A.COA43LCOME_DES = B.COA43LCOME AND            
        A.COAZFLTERM_DES = B.COAZFLTERM)                            
     where A.COAZ7FOPER <= :WS-SYSIN-FECHA              
     and A.COA43LCOME_DES not in (999008881)            
     and A.COAZ7CTIOP in ('P')                          
     and A.COAZ7CRESU in ('a', 'A', 'd', 'D')          
     ORDER BY B.C00LTIPO_ADQ, B.C00LCSB_ADQ            
 END-EXEC.                                              



 INITIALIZE DCLTOAZ7S1 DCLTOAZFS1                


    EXEC SQL                                        
       FETCH CTOAZ7OA INTO                     :DCLTOAZFS1.C00LTIPO-ADQ  
                                               :NULL-C00LTIPO-ADQ        
                                              ,:DCLTOAZFS1.C00LCSB-ADQ  
                                               :NULL-C00LCSB-ADQ        
                                              ,:DCLTOAZ7S1.COAZ7CRESU    
                                               :NULL-COAZCRESU          
    END-EXEC                                        

The definition of variables:

    COAZ7CRESU                     CHAR(1) NOT NULL
    C00LTIPO_ADQ                   SMALLINT NOT NULL
    C00LCSB_ADQ                    SMALLINT NOT NULL 

With null indicator works perfect, but, I don't understand. Somebody, Could You explain me? Thanks

Answer 1

The problem is the LEFT JOIN, this will return every matching entry in Table 1 and nulls for the table 2 when there is no matching entry.

You need to code a null indicator (or flag) in your SQL. see Handling Null Characters or Google it

basically

 EXEC SQL
   FETCH MY-CURSOR INTO 
      :FIELD1-A,
      :FIELD2-B  :FIELD2-B-NULL-INDICATOR, 
      :FIELD3-B  :FIELD3-B-NULL-INDICATOR
 END-EXEC.

where FIELD2-B-NULL-INDICATOR and FIELD3-B-NULL-INDICATOR are the null flags for fields FIELD2 and FIELD3.

---

An example of what is happening

If this is the input tables

 Table1
    Field1  Field5 
    1        1
    2        5
    3        6
    4        4

 Table2
   Field5    Field2  Field3
   1         11      12
   4         41      44

then the result of

  SELECT A.FIELD1, B.FIELD2, B.FIELD3
  FROM TABLE1 AS A LEFT JOIN TABLE2 AS B ON A.FIELD4 = B.FIELD5  

will be

  Field1    Field2  Field3
  1         11      12 
  2         null    null     - null because there is no matching entry in table 2
  3         null    null
  4         41      42

So the Left join introduces the possibility of Nulls