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.

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

  1. 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 
    ...
  2. 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
    ........
  3. 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 broker if you chose to handle database errors in the message flow.

Related concepts
Message flows overview
Exception list tree structure
ESQL overview
Message modeling
Related tasks
Designing a message flow
Defining message flow content
Handling errors in message flows
Managing ESQL files
Related reference
Compute node
Database node
Filter node
ESQL reference
SET statement
ESQL simple comparison operators
CARDINALITY function
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2009Copyright IBM Corporation 1999, 2009.
Last updated : 2009-01-07 15:20:22

ac17020_