MQSeries.net :: View topic - Without using ITEM in the SELECT statement, how can I get

narendra · Posted: Wed Jun 19, 2002 8:49 am Post subject:

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 ?