Will SQLSTATE ever be 00000 with a negative null indicator?

Question

Is it possible to have a negative null indicator with an SQLSTATE of 00000?

I am wondering if checking for both the SQLSTATE and the null indicator value is needed for certain operations, like an if statement. Here is an example of what I am working with:

EXEC SQL                                                                           
  SELECT 1 INTO :x1RcdFound:x1IndicatorVariable                                               
    FROM table1                                                                   
      WHERE value = :givenValue                                                    
       AND value2 = 'helloWorld';              

//this is where I am wondering if both checks need to happen                   
**if ( sqlstate = SQL_OK and x1RcdFound = 1 );**

Although this is a simple example, there are times when this is ran through a large loop, or multiple statements need to be used and saving a little bit of run time is helpful in the long run. This is part of an effort to write as concise code as possible, which is often overlooked in procedural languages.

Answer 1

If a NULL value is possible, thus you need a NULL indicator, then yes, you must check it after getting an SQLSTATE of 00000.

However, NULL indicator's are only needed if a column could be NULL.

In the specific case you've provided code for that will not happen, you don't need a NULL indicator.

EXEC SQL                                                                           
  SELECT 1 INTO :x1RcdFound
    FROM table1                                                                   
      WHERE value = :givenValue                                                    
       AND value2 = 'helloWorld';

You're either going to get a 1 or you're going to get no rows. Which is not the same as RS that contains a column that is NULL.

In fact, if there no rows, you'll get SQLSTATE = '02000' --> NO_DATA

Most code like yours I see takes advantage of the fact that if there are no rows, or if something goes wrong, the host variable is unchanged...

dcl-s xlRcdFound ind;

// note the definition of xlRcdFound as indicator
//   ie.  char(1) used as a Boolean.
xlRcdFound = *OFF;
EXEC SQL                                                                           
  SELECT '1' INTO :x1RcdFound
    FROM table1                                                                   
      WHERE value = :givenValue                                                    
       AND value2 = 'helloWorld';

if SqlExceptionOccurred(SQLSTATE:*ON);
  //report SQL exception
elseif xlRcdFound;
  //do something
endif;


dcl-proc SqlExceptionOccurred;
dcl-pi *n ind;
   mySqlState char(5) value;
   allowNoData ind value;
end-pi;
  if allowNoData;
     return %subst(mySqlState:1:2) > '02';
  endif;
     return %subst(mySqlState:1:2) >= '02';
end-proc;