| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| facing problem when Esql database query returns NULL |
« View previous topic :: View next topic » |
| Author |
Message
|
| Harshalw |
 Posted: Mon Aug 25, 2008 6:20 am Post subject: facing problem when Esql database query returns NULL |
 |
|
Voyager
Joined: 23 Jul 2008
Posts: 77
|
Hi
This is my esql stmt
Set Environment.Variables.Cagwx = THE (Select ITEM CTR.PRICECODE from Database.SAPR3.PRICETYPE_CTR AS CTR
WHERE CTR.PRICETYPE = InputRoot.XMLNSC.wwprttxn.price[I].pricetype
AND CTR.COUNTRY = InputRoot.XMLNSC.wwprttxn.price[I].country);
where sometimes the db2 query returns 0 rows , so in this case what wld be value of Environment.Variables.Cagwx ? How to check for no rows found condition in this case. since i need to do some different processing for no rows found.
I am checking sqlcode which gives me 0 even if no rows are there?
plz reply
Thanks
Harshalw |
|
| Back to top |
|
 |
| marko.pitkanen |
| Posted: Mon Aug 25, 2008 6:44 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Hi,
Perhaps you can use one of these
| Quote: |
WebSphere Message Broker Version 6 Release 0 ESQL
.
.
.
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.XML.Testcase.Results[] = ( SELECT T.MYCOL FROM Database.MYTABLE) ...... IF CARDINALITY (OutputRoot.XML.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.XML.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.
|
Marko |
|
| Back to top |
|
|
| Harshalw |
| Posted: Mon Aug 25, 2008 10:25 pm Post subject: |
|
|
Voyager
Joined: 23 Jul 2008
Posts: 77
|
It worked!!
Thanks
marko.pitkanen |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
