MQSeries.net :: View topic

SpitFire · Posted: Fri Jan 20, 2006 5:48 am Post subject:

Hi all,
In one of my message flows (designed in WBIMB V5.0 CSD-5), I'm trying to retrieve an information from an incoming message and then query the database and then construct the outgoing message accordingly. I'm facing multiple problems in this regard.
I have quoted them all here and please help me in resolving them.

Message Structure: XML

- Incoming Message -
<Request>
<Items>
<ItemNumber> </ItemNumber>
<RequiredInfo> NAME/ORIGIN/PLACE </RequiredInfo>
</Items>
</Request>

- Outgoing Message -
<Response>
<Items>
<ItemNumber>
</ItemNumber>
<SuppliedInfo> NAME/ORIGIN/PLACE </RequiredInfo>
</Items>
</Response>

SQL query:
--------------------------------------------------------------------------------
WHILE Index <= CARDINALITY(InputRoot.MRM.Items[])
IF InputRoot.MRM.Request.Items[Index].RequiredInfo = 'NAME' THEN
SET OutputRoot.MRM.Response.SuppliedInfo[PartIndex].PART_INFO
VALUE =
THE(SELECT ITEM COALESCE(T.NAME, 'NULL') FROM
Database.TAB_SOME_TABLE AS T
WHERE
T.ITEM_NUM
LIKE
InputRoot.MRM.Request.Items[Index].ItemNumber);
END IF;
Index = Index + 1;
END WHILE;
--------------------------------------------------------------------------------

[1] I don't know how to retrieve the result of an SQL query and store the answer in a scalar value. The above query fails with the error 'Invalid Field Reference'. Is my method of extracting a scalar value out of an sql query incorrect?

[2] In the same query indicated above, can I include the field name to be extracted from the resultset dynamically. What I mean is can the above query be formulated like this? The column to be extracted is dependent on the value of - "InputRoot.MRM.Items[Index].RequiredInfo".

Modified - SQL query:
--------------------------------------------------------------------------------
WHILE Index <= CARDINALITY(InputRoot.MRM.Items[])
IF InputRoot.MRM.Request.Items[Index].RequiredInfo = 'NAME' THEN
SET OutputRoot.MRM.Response.SuppliedInfo[PartIndex].PART_INFO
VALUE =
THE(SELECT ITEM COALESCE(<<Required Info>>, 'NULL') FROM
Database.TAB_SOME_TABLE AS T
WHERE
T.ITEM_NUM
LIKE
InputRoot.MRM.Request.Items[Index].ItemNumber);
END IF;
Index = Index + 1;
END WHILE;
--------------------------------------------------------------------------------
_________________
...: 5|71+ph1|23 :...