If a SELECT statement returns no data, or no further data, this is handled as a normal situation and no error code is set in SQLCODE. This occurs regardless of the setting of the Throw Exception On Database Error and Treat Warnings As Errors properties on the current node.
To recognize that a SELECT statement has returned no data, include ESQL that checks what has been returned. You can do this in a number of ways:
This returns a Boolean value that indicates if a SELECT function returned one or more values (TRUE), or none (FALSE).
IF EXISTS(SELECT T.MYCOL FROM Database.MYTABLE) THEN ...
If you expect an array in response to a SELECT, you can use CARDINALITY to calculate how many entries have been received.
SET OutputRoot.XMLNS.Testcase.Results[] = ( SELECT T.MYCOL FROM Database.MYTABLE) ...... IF CARDINALITY (OutputRoot.XMLNS.Testcase.Results[])> 0 THEN ........
If you have used either THE or ITEM keywords in your SELECT statement, a scalar value is returned. If no rows have been returned, the value set is NULL. However, it is possible that the value NULL is contained within the column, and you might want to distinguish between these two cases.
To do this include COALESCE in the SELECT statement, for example:
SET OutputRoot.XMLNS.Testcase.Results VALUE = THE ( SELECT ITEM COALESCE(T.MYCOL, 'WAS NULL') FROM Database.MYTABLE);
If this returns the character string WAS NULL, this indicates that the column contained NULL, and not that no rows were returned.
In previous releases, an SQLCODE of 100 was set in most cases if no data, or no further data, was returned. An exception was raised by the broker if you chose to handle database errors in the message flow.