| Author |
Message
|
| raj_ebiz |
 Posted: Thu Aug 17, 2006 10:36 am Post subject: SELECT accessing both DB and MessageTree |
 |
|
Novice
Joined: 19 Oct 2002
Posts: 11
|
Hi,
Are there any issues in accessing both Database and Message Tree in the same SELECT statement.
I have a SELECT statement like below
inRef is a pointer to a element in the MessageTree
SELECT T as OrderLine FROM inRef.OrderLine[] as T , Database.ITEM as L Where T.ItemId=L.ITEMID
The above statement didnot return any rows. When i looked into the usertrace, i see that T.ItemId is always NULL.
Note:
SELECT T as OrderLine FROM inRef.OrderLine[] as T
works fine.
Just wondering if anyone had to perform something like this before.
Thanks |
|
| Back to top |
|
 |
| shrek |
| Posted: Fri Aug 18, 2006 2:42 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
| Can you post your message tree. Thanks. |
|
| Back to top |
|
|
| raj_ebiz |
| Posted: Fri Aug 18, 2006 5:58 am Post subject: SELECT accessing both DB and Message Tree |
|
|
Novice
Joined: 19 Oct 2002
Posts: 11
|
Following is the message i am using. This is just a sample xml i am using in a test flow just to see if it works.
<Order>
<OrderLine>
<LineNum>1</LineNum>
<ItemId>123</ItemId>
</OrderLine>
<OrderLine>
<LineNum>2</LineNum>
<ItemId>456</ItemId>
</OrderLine>
</Order>
Thanks, |
|
| Back to top |
|
|
| raj_ebiz |
| Posted: Mon Aug 21, 2006 10:58 am Post subject: |
|
|
Novice
Joined: 19 Oct 2002
Posts: 11
|
| Still wondering if anyone has done something like this before ? The ESQL reference does say that we can access both DB and MessageTree in the same select statement. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Aug 21, 2006 11:39 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
Hi,
This kind of JOIN between a DB and a message tree is tested and works fine. If you are getting no rows, it is because no rows match your WHERE clause. What is in the DB in this case?
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| raj_ebiz |
| Posted: Mon Aug 21, 2006 1:54 pm Post subject: |
|
|
Novice
Joined: 19 Oct 2002
Posts: 11
|
Thanks for your response MGK.
The DB has a matching row for my WHERE clause.
Below is a sample ESQL i am testing with.
SELECT T as OrderLine FROM inRef.OrderLine[] as T , Database.ITEM as L Where T.ItemId=L.ITEMID
When i run a UserTrace, i see that T.ItemId is always evaluating to NULL and hence the Where clause is always returns false.
Thanks, |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Aug 22, 2006 4:49 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
Can you post the table definition and sample contents? Also can you post the Message Broker version and CSD, and the DB type (e.g. DB2?) and version.
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Aug 22, 2006 4:57 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Also, post a trace of inRef.OrderLine[].
it's probably not what you think it is.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| raj_ebiz |
| Posted: Tue Aug 22, 2006 10:20 am Post subject: |
|
|
Novice
Joined: 19 Oct 2002
Posts: 11
|
My Broker version is 5 and CSD 05. I am running the locally on Win XP.
I have created a test table in the Broker's database. The database is DB2 8.2.
Table has only 2 rows.
select * from ITEMLIST
ITEMID
----------
999
456
2 record(s) selected.
My test message flow is simple. MQInput->Compute->MQOutput.
Input XML is like below.
<Order>
<OrderLine>
<LineNum>1</LineNum>
<ItemId>123</ItemId>
</OrderLine>
<OrderLine>
<LineNum>2</LineNum>
<ItemId>456</ItemId>
</OrderLine>
</Order>
Compute node has the following ESQL.
CALL CopyMessageHeaders();
DECLARE inRef REFERENCE to InputBody.Order ;
SET OutputRoot.XML.Order.OrderLine[] = (SELECT ITEM T FROM inRef.OrderLine[] as T , Database.ITEMLIST as L Where T.ItemId=L.ITEMID );
RETURN TRUE;
What is surprising me is that the above SELECT statement looks different in the UserTrace.
BIP2537I: Node 'TestSelect.Compute': Executing statement 'DECLARE inRef REFERENCE TO InputBody.Order;' at (.TestSelect_Compute.Main, 4.4).
BIP2537I: Node 'TestSelect.Compute': Executing statement 'SET OutputRoot.XML.Order.OrderLine[ ] = (SELECT ITEM T FROM DATABASE(SELECT * FROM ITEMLIST L WHERE (?)=(L.ITEMID), T.ItemId), inRef.OrderLine[ ] AS T);' at (.TestSelect_Compute.Main, 7.4).
BIP2538I: Node 'TestSelect.Compute': Evaluating expression '(SELECT ITEM T FROM DATABASE(SELECT * FROM ITEMLIST L WHERE (?)=(L.ITEMID), T.ItemId), inRef.OrderLine[ ] AS T)' at (.TestSelect_Compute.Main, 7.56).
BIP2573W: Node 'TestSelect.Compute': (.TestSelect_Compute.Main, 7.56) : Finding first SELECT result.
BIP2538I: Node 'TestSelect.Compute': Evaluating expression 'DATABASE(SELECT * FROM ITEMLIST L WHERE (?)=(L.ITEMID), T.ItemId)' at (, 1.1).
BIP2538I: Node 'TestSelect.Compute': Evaluating expression 'T.ItemId' at (.TestSelect_Compute.Main, 7.118).
BIP2544I: Node 'TestSelect.Compute': Executing database SQL statement 'SELECT * FROM ITEMLIST L WHERE (?)=(L.ITEMID)' derived from (, 1.1); expressions 'T.ItemId'; resulting parameter values 'NULL'.
BIP2570W: Node 'TestSelect.Compute': (.TestSelect_Compute.Main, 7.56) : There were no items in the FROM clause satisfying the WHERE clause.
BIP2562I: Node 'TestSelect.Compute': Assigning a list to 'OutputRoot.XML.Order.OrderLine[]'.
BIP2537I: Node 'TestSelect.Compute': Executing statement 'RETURN TRUE;' at (.TestSelect_Compute.Main, 10.3).
Any pointers would be helpful.
Thanks |
|
| Back to top |
|
|
|
|
