Checking returns to SELECT

If a SELECT function returns no data, or no further data, this result is handled as a normal situation and no error code is set in SQLCODE, regardless of the setting of the Throw Exception On Database Error and Treat Warnings As Errors properties on the current node.

About this task

To recognize that a SELECT function has returned no data, include ESQL that checks what has been returned. You can use various methods:

EXISTS
This ESQL 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 
...
```

CARDINALITY

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

IS NULL
If you have used either THE or ITEM keywords in your SELECT function, 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.

Distinguish between cases by including COALESCE in the SELECT function, for example:
```
SET OutputRoot.XMLNS.Testcase.Results VALUE = THE (
   SELECT ITEM COALESCE(T.MYCOL, 'WAS NULL')
   FROM Database.MYTABLE);
```
If this example returns the character string WAS NULL, it 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 integration node if you chose to handle database errors in the message flow.