|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
|
|
Without using ITEM in the SELECT statement, how can I get |
« View previous topic :: View next topic » |
Author |
Message
|
narendra |
Posted: Wed Jun 19, 2002 7:42 am Post subject: Without using ITEM in the SELECT statement, how can I get |
|
|
Apprentice
Joined: 04 Jun 2002 Posts: 26
|
Hi,
I would like some assistance.
I successfully use the PROPAGATE function to create multiple messages from the result
sets returned by select statements. However I run into some problem using the
ITEM key word when one of the col is NULL in the select statement.
THIS IS THE CONTENT OF THE COMPUTE NODE
******************* BEGIN COMPUTE *********************************
DECLARE I INTEGER;
SET I = 1;
WHILE I < CARDINALITY(InputRoot.*[]) DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I=I+1;
END WHILE;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.
-- This compute node will propagate each row from the staff table into one separate message (per row)
DECLARE i INTEGER;
Set i =1;
SET InputDestinationList.XML.TempData."ID"[]= ( SELECT ITEM A.ID
from Database.STAFF AS A WHERE A.ID < 30 );
SET InputDestinationList.XML.TempData."NAME"[]= ( SELECT ITEM A.NAME
from Database.STAFF AS A WHERE A.ID < 30 );
SET InputDestinationList.XML.TempData."JOB"[]= ( SELECT ITEM A.JOB
from Database.STAFF AS A WHERE A.ID < 30 );
WHILE i <= CARDINALITY(InputDestinationList.XML.TempData."ID"[])
DO
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
SET OutputRoot.XML.MSG."id" = InputDestinationList.XML.TempData."ID"[i];
SET OutputRoot.XML.MSG."name" = InputDestinationList.XML.TempData."NAME"[i];
SET OutputRoot.XML.MSG."job" = InputDestinationList.XML.TempData."JOB"[i];
PROPAGATE;
SET i = i+1;
END WHILE;
RETURN FALSE;
**************** END COMPUTE ********************
THIS IS THE OUTPUT DATA
****************begin output1**************************
<MSG>
<id>10</id>
<name>Sanders</name>
<job>Mgr </job>
</MSG>
****************end output1*********************************
please note using ITEM in the SELECT statement will format it nicely. However if NAME or JOB column in staff table contains NULL, SELECT ITEM will fail
without using ITEM in the above SELECT , the output looks like
**************begin output2**************************
<MSG>
<id>
<ID>10</ID>
</id>
<name>
<NAME>Sanders</NAME>
</name>
<job>
<JOB>Mgr </JOB>
</job>
</MSG>
***************end of output2***********************************
IF NAME contains NULL, output2 will look like this using ITEM keyword in the select statement
<MSG>
<id>
<ID>10</ID>
</id>
<name>
<NAME></NAME>
</name>
<job>
<JOB>Mgr </JOB>
</job>
</MSG>
****************** begin of mqsi broker trace ***************
2002-06-19 13:35:27.281000 2544 UserTrace BIP2567I: Node 'EORDER_2.Compute1': Assigning NULL to 'InputDestinationList.XML.TempData.A.NAME[1]', thus deleting it.
2002-06-19 13:35:27.281000 2544 UserTrace BIP2231E: Error detected whilst processing a message 'EORDER_2.Compute1'.
The message broker detected an error whilst processing a message in node 'EORDER_2.Compute1'. The message has been augmented with an exception list and has been propagated to the node's failure terminal for further processing.
See the following messages for details of the error.
2002-06-19 13:35:27.281000 2544 RecoverableException BIP2333E: Error attaching field '' as a sibling of field 'A.NAME' which has no parent.
The message broker attempted to attach the field '' as a sibling of the field 'A.NAME' but the latter field does not have a parent field.
This error can only arise as a result of an error in a node or parser implementation library. Contact the author or supplier of the loadable implementation library and obtain a correct version.
*******************end of mqsi brokertrace
my question is:
Without using ITEM in the SELECT statement, how can I get output1 as the desired output ?
|
|
Back to top |
|
|
kirani |
Posted: Wed Jun 19, 2002 8:49 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001 Posts: 3779 Location: Torrance, CA, USA
|
You can not use InputDestinationList to store temp values returned from database. Input trees are read only and cannot be modified using ESQL. You need to change your code to use OutputDestinationList instead.
Try changing your code to following and see if you get desired output or not.
Code: |
DECLARE I INTEGER;
SET I = 1;
WHILE I < CARDINALITY(InputRoot.*[]) DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I=I+1;
END WHILE;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.
-- This compute node will propagate each row from the staff table into one separate message (per row)
DECLARE i INTEGER;
Set i =1;
DECLARE TCNT INTEGER;
SET OutputDestinationList.Variables.TempData[]= ( SELECT A.ID, A.NAME, A.JOB
from Database.STAFF AS A WHERE A.ID < 30 );
SET TCNT = CARDINALITY(OutputDestinationList.Variables.TempData[]);
WHILE i <= TCNT
DO
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
SET OutputRoot.XML.MSG."id" = OutputDestinationList.Variables.TempData[i]."ID";
SET OutputRoot.XML.MSG."name" = OutputDestinationList.Variables.TempData[i]."NAME";
SET OutputRoot.XML.MSG."job" = OutputDestinationList.Variables.TempData[i]."JOB";
PROPAGATE;
SET i = i+1;
END WHILE;
RETURN FALSE;
|
_________________ Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
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
|
|
|
|