WebSphere Message Brokers
File: ac17020_
Writer: Bill Oppenheimer

Task topic

This build: July 31, 2007 21:18:48

Checking returns to SELECT

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:

  1. EXISTS

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

Related concepts
Message flows overview
ExceptionList 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, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:18:48

ac17020_ This topic's URL is: