| Author |
Message
|
| harelmoshe1 |
 Posted: Thu Mar 09, 2006 12:20 am Post subject: SELECT trouble |
 |
|
Apprentice
Joined: 29 Dec 2005
Posts: 41
|
I'm trying to excute the following statement:
Set OutputRoot.XMLNS.Reply[] =
SELECT CL.*
FROM ConnLog AS CL
WHERE ID = '200509000';
by i'm getting an error about non-matching type (list with non-list).
As i need ALL the rows returned by SELECT put on the OutputRoot tree, i can't use THE or ITEM...
What can i do to make it look like:
OutpuRoot
--> XMLNS
------> Reply
------------> record1
------------- ... all fields ...
------------> record2
-------------- ... all fields ...
and so on ? |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Thu Mar 09, 2006 12:59 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi harelmoshe1,
The below if from the ESQL pdf.
| Quote: |
Differences between message and database selections
FROM expressions in which a correlation variable represents a row in a message behave slightly differently from those in which the correlation variable represents a row in a real database table.
In the message case, a path involving a star (*) has the normal meaning; it ignores the field’s name and finds the first field that matches the other criteria (if any).
In the database case a star (*) has, for historical reasons, the special meaning of “all fields”. This special meaning requires advance knowledge of the definition of the database table and is only supported when querying the default database (that is, the database pointed to by the node’s data source attribute). For example, the following queries return column name/value pairs only when querying the default database: |
I think you are accessing the message and hence you are not getting a list...thus the mismatch.
I dont think I need to tell you how to fix it 
Regards. |
|
| Back to top |
|
|
| harelmoshe1 |
| Posted: Thu Mar 09, 2006 2:47 am Post subject: |
|
|
Apprentice
Joined: 29 Dec 2005
Posts: 41
|
Thanks for your response, elvis_gn -
but i've mistyped the query, so it really looks like:
Set OutputRoot.XMLNS.Reply[] =
SELECT CL.*
FROM Database.stap.ConnLog AS CL
WHERE ID = '200509000';
It's actually a database query and not a message one...
Shouldn't it return all the fields in the mathcing rows, it that case ? |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Thu Mar 09, 2006 2:53 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi harelmoshe1,
Your select should not be
| Code: |
| Set OutputRoot.XMLNS.Reply[] = SELECT CL.* FROM Database.stap.ConnLog AS CL WHERE ID = '200509000'; |
but this
| Code: |
| Set OutputRoot.XMLNS.Reply[] = SELECT CL.* FROM Database.stap.ConnLog AS CL WHERE CL.ID = '200509000'; |
Also, what is Database.stap.ConnLog...is ConnLog the name of the Table ?
Please run this code and tell us the error(paste the entire log), if any.
Regards. |
|
| Back to top |
|
|
| harelmoshe1 |
| Posted: Thu Mar 09, 2006 6:52 am Post subject: |
|
|
Apprentice
Joined: 29 Dec 2005
Posts: 41
|
The problem is that the code won't even deploy, not at run-time.
I also tried:
SET OutputBody.Reply[] =
SELECT Database.wbistar.conn_log AS CL
WHERE CL.Data = messageID AND
CL.FailureCount =
THE(SELECT MAX(CLG.FailureCount)
FROM Database.wbistar.conn_log AS CLG
WHERE CLG.Data = messageID);
messageID is an integer.
Still i the following error while trying to deploy:
'expected CL but found ';'
I fought with it for about 2 hours of syntax chaging and rewriting, what am i missing ?  |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Mar 09, 2006 6:54 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Can you post all related exceptions please
_________________
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 |
|
|
| mgk |
| Posted: Thu Mar 09, 2006 7:09 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Just FYI,
I tried the code from elvis and it worked first time for me.
My ESQL (edited for my environment was):
| Code: |
| SET OutputRoot.XML.Test.newReply[] = (SELECT CL.* FROM Database.assignids AS CL WHERE CL.ID = 'ABCDE'); |
And I got 3 rows returned as expected.
Which version and fixpack of the broker are you running? If you are not on V6 then you will need brackets ( ) around the SELECT as I show in my code snipit.
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 |
|
|
| harelmoshe1 |
| Posted: Thu Mar 09, 2006 8:06 am Post subject: |
|
|
Apprentice
Joined: 29 Dec 2005
Posts: 41
|
Thanks !
It was really a brackets problem...
I actually apent 3 hours on it  |
|
| Back to top |
|
|
|
|
