| Author |
Message
|
| ltopa |
 Posted: Wed Mar 07, 2007 10:54 pm Post subject: SELECT function to select message parts in compute node |
 |
|
Newbie
Joined: 07 Mar 2007
Posts: 7
|
I am trying to use the following select statements in an ESQL file for a compute node:
| Code: |
FOR instId AS Environment.Variables.instId[] DO
SET Environment.Variables.ivPosition[] = (SELECT P.* FROM Environment.Variables.ivCdxPosition[] AS P WHERE P.PARENT_INST_ID = instId);
-- TODO: now use ivPosition[]
END FOR; |
where ivCdxPosition[] is a flat array of rows returned by a previous database query. It looks like I can make the above SELECT statement work only if I replace P.* with P.COL_A, P.COL_B, etc and if I use a hardcoded value instead of instId as a "bind" variable (something like WHERE P.PARENT_INST_ID = 12345). What am I doing wrong?
I am also trying to do something similar but using an inner select:
| Code: |
FOR instId AS Environment.Variables.instId[] DO
SET Environment.Variables.ivSecurity[] = (SELECT I.* FROM Environment.Variables.ivCdxSecurity[] AS I WHERE I.INST_ID IN
(SELECT P.CHILD_INST_ID FROM Environment.Variables.ivCdxPosition[] AS P WHERE P.PARENT_INST_ID = instId));
-- TODO: now use ivSecurity[]
END FOR; |
When I have the above SELECT, the toolkit doesn't show any errors or warnings, but when I deploy the bar file I get the error:
BIP2467E: (dev.main.ConsumeCore.Update, {1}) : Incompatible operands for IN predicate.
The values on the right of an IN predicate must be compatible with the data type of the expression on the left.
Correct the syntax of your ESQL expression in node 'dev.main.ConsumeCore.Update', around line and column '{1}', then redeploy the message flow.
I am using WMB Toolkit version 6.0.2. What am I doing wrong?? |
|
| Back to top |
|
 |
| au@kosa |
| Posted: Thu Mar 08, 2007 3:06 am Post subject: |
|
|
Centurion
Joined: 04 Jan 2007
Posts: 103
Location: pune
|
try this
Encapsulate your query inside passthru ESQL function
_________________
Regards,
au@kosa
IBM Certified SOA Solution Designer/Associate |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Mar 08, 2007 5:07 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
au@kosa: PASSTHRU can only be used for DATABASE queries, not message tree queries.
ltopa: The second example you post does not work because ESQL does not support a SELECT inside an IN (but it does support nested selects in the general case).
What is the error you are getting in your first example? You do not say exactly what the problem is...
Also, what version of the broker are you using?
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 |
|
|
| ltopa |
| Posted: Thu Mar 08, 2007 5:55 am Post subject: |
|
|
Newbie
Joined: 07 Mar 2007
Posts: 7
|
mgk: the problem with my first code snippet is that I can't seem to get "SELECT P.* ... WHERE P.INST_ID = mylocalVar" to return anything. No errors, it just doesn't select anything (I traced it with debugger as well). Only if I replace my select with "SELECT P.COL_A, P.COL_B ... WHERE P.INST_ID = 12345", I get a selection back. So, 2 questions:
1. how can I use "bind" variables in my select?
2. how can I use a wild card to get all the fields? I have many columns and I would like to avoid referring to them explicitly.
As for my second code snippet, if SELECTs are not supported inside an IN clause, how can I do what I need?
The version of the broker is 6.0
au@kosa: I would like to avoid using a PASSTHRU because it would be an expensive query, actually many expensive queries (notice I have a FOR loop)
Thanks! |
|
| Back to top |
|
|
| gregop |
| Posted: Fri Mar 09, 2007 12:04 am Post subject: |
|
|
Voyager
Joined: 24 Nov 2006
Posts: 81
|
P.* means different things depending on wether you are selecting from a database or message tree.
For database it returns all values from the table.
For message tree it is used (as is normal in esql) to return the first child. So I'd expect you'd get the first child of Environment.Variables.ivCdxPosition.
What do you get if use
SET Environment.Variables.ivPosition[] = (SELECT P FROM........ ? |
|
| Back to top |
|
|
| vsr |
| Posted: Fri Mar 09, 2007 9:40 am Post subject: Re: SELECT function to select message parts in compute node |
|
|
Centurion
Joined: 04 Apr 2006
Posts: 104
|
| ltopa wrote: |
FOR instId AS Environment.Variables.instId[] DO
|
the variabe 'instId' is an array as per your code
| Code: |
| WHERE P.PARENT_INST_ID = instId |
And you are assigning "bind" variable to array rather than a static value ..
I guess your second 'instId' should be different . |
|
| Back to top |
|
|
| ltopa |
| Posted: Fri Mar 09, 2007 7:52 pm Post subject: Re: SELECT function to select message parts in compute node |
|
|
Newbie
Joined: 07 Mar 2007
Posts: 7
|
| vsr wrote: |
the variabe 'instId' is an array as per your code
| Code: |
| WHERE P.PARENT_INST_ID = instId |
And you are assigning "bind" variable to array rather than a static value ..
I guess your second 'instId' should be different . |
But isn't it true that the instId used for the FOR loop is a different variable from Environment.Variables.instId[] ?
Anyway, I think I tried using a different variable name in the FOR loop and I still didn't get any selection -- I'll try again on Monday.
In summary, this is what I have observed so far.
This works (example A):
| Code: |
| SET Environment.Variables.ivPosition[] = (SELECT P.COL_A, P.COL_B FROM Environment.Variables.ivCdxPosition[] AS P WHERE P.PARENT_INST_ID = 12345); |
Environment.Variables.ivPosition[] gets assigned an array with EV.ivPosition[1].COL_A, EV.ivPosition[1].COL_B, ... EV.ivPosition[n].COL_A, EV.ivPosition[n].COL_B
This doesn't work (example B):
| Code: |
| SET Environment.Variables.ivPosition[] = (SELECT P.* FROM Environment.Variables.ivCdxPosition[] AS P WHERE P.PARENT_INST_ID = 12345); |
Environment.Variables doesn't get assigned any child node named ivPosition.
And this doesn't work (example C):
| Code: |
| SET Environment.Variables.ivPosition[] = (SELECT P.COL_A, P.COL_B FROM Environment.Variables.ivCdxPosition[] AS P WHERE P.PARENT_INST_ID = myVar); |
Environment.Variables again doesn't get assigned any children (ok, the original example I posted shows instId instead of myVar, but I think I tried with myVar and still nothing)
I'll try SELECT P FROM ... but I go back to my two questions:
1. should I be able to select all direct children from ivCdxPosition[]? how?
2. can I use bind variables with SELECT function that operates on message trees? All the examples I have been able to find have a hardcoded value or they are database queries... |
|
| Back to top |
|
|
| mgk |
| Posted: Sun Mar 11, 2007 4:16 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| 1. should I be able to select all direct children from ivCdxPosition[]? how? |
Yes, this should work. All three of your examples (A,B & C) look to me like they should work. Therefore (seeing as B & C do not work for you) I need more information. Can you wire up a trace node before and after the compute node that traces ${Envrionment} and paste the output here. Also can you post your Compute node code in full (as I want to see how you set up the evironment tree, and myVar etc.) In addition if the Envrionment is loaded from a message, can you post a sample message as well.
| Quote: |
| 2. can I use bind variables with SELECT function that operates on message trees? All the examples I have been able to find have a hardcoded value or they are database queries... |
Yes this should work for message trees as well.
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 |
|
|
| ltopa |
| Posted: Mon Mar 12, 2007 3:48 pm Post subject: SELECT function to select message parts in compute node |
|
|
Newbie
Joined: 07 Mar 2007
Posts: 7
|
| Quote: |
| Can you wire up a trace node before and after the compute node that traces ${Envrionment} and paste the output here. |
The ${Environment} trace before the compute node is empty, since this is the first node after my mqinput node. Here is the ${Environment} trace after the compute node:
| Code: |
(
(0x01000000):Variables = (
(0x01000000):ivCdxSecurity = (
(0x03000000):INST_ID = 8.054543E+6
(0x03000000):INSTRUMENT_ID = '0610-99DT006W-52108H7K'
(0x03000000):INSTRUMENT_TYP = 610
)
(0x01000000):ivCdxSecurity = (
(0x03000000):INST_ID = 8.054584E+6
(0x03000000):INSTRUMENT_ID = '0620-99DT006W-52108H7K'
(0x03000000):INSTRUMENT_TYP = 620
)
(0x01000000):ivCdxSecurity = (
(0x03000000):INST_ID = 8.054609E+6
(0x03000000):INSTRUMENT_ID = '0630-99DT006W-52108H7K'
(0x03000000):INSTRUMENT_TYP = 630
)
(0x01000000):ivCdxPosition = (
(0x03000000):PARENT_INSTRUMENT_ID = '0610-99DT006W-52108H7K'
(0x03000000):PARENT_INSTRUMENT_TYP = 610
(0x03000000):PARENT_INST_ID = 8.054543E+6
(0x03000000):CHILD_INSTRUMENT_ID = '0620-99DT006W-52108H7K'
(0x03000000):CHILD_INSTRUMENT_TYP = 620
(0x03000000):CHILD_INST_ID = 8.054584E+6
(0x03000000):SEC_ID = NULL
(0x03000000):UNITS = 1E+0
(0x03000000):UNITS_FACE = NULL
)
(0x01000000):ivCdxPosition = (
(0x03000000):PARENT_INSTRUMENT_ID = '0610-99DT006W-52108H7K'
(0x03000000):PARENT_INSTRUMENT_TYP = 610
(0x03000000):PARENT_INST_ID = 8.054543E+6
(0x03000000):CHILD_INSTRUMENT_ID = '0630-99DT006W-52108H7K'
(0x03000000):CHILD_INSTRUMENT_TYP = 630
(0x03000000):CHILD_INST_ID = 8.054609E+6
(0x03000000):SEC_ID = NULL
(0x03000000):UNITS = -1E+0
(0x03000000):UNITS_FACE = NULL
)
(0x01000000):ivCdxPosition = (
(0x03000000):PARENT_INSTRUMENT_ID = '0600-99DT006W'
(0x03000000):PARENT_INSTRUMENT_TYP = 600
(0x03000000):PARENT_INST_ID = 8.054568E+6
(0x03000000):CHILD_INSTRUMENT_ID = '0610-99DT006W-52108H7K'
(0x03000000):CHILD_INSTRUMENT_TYP = 610
(0x03000000):CHILD_INST_ID = 8.054543E+6
(0x03000000):SEC_ID = NULL
(0x03000000):UNITS = 4E-2
(0x03000000):UNITS_FACE = NULL
)
(0x01000000):cdsInstrIds = (
(0x03000000):INSTRUMENT_ID = '0610-99DT006W-52108H7K'
)
(0x01000000):instrId = (
(0x03000000):INSTRUMENT_ID = '0610-99DT006W-52108H7K'
)
(0x03000000):startTime = TIMESTAMP '2007-03-12 19:09:36.696984'
(0x03000000):msgTimestamp = '2007-02-27T10:49:01'
(0x03000000):publish = TRUE
)
) |
| Quote: |
| Also can you post your Compute node code in full (as I want to see how you set up the evironment tree, and myVar etc.) |
The entire file is too big and it has code for many other business cases that don't get used for this case. I'll post the relevant pieces in a new post, since I'm having difficulties doing it all in one post. |
|
| Back to top |
|
|
| ltopa |
| Posted: Mon Mar 12, 2007 4:00 pm Post subject: SELECT function to select message parts in compute node |
|
|
Newbie
Joined: 07 Mar 2007
Posts: 7
|
funny, posting function on this forum seems to choke if I have the word PASSTHR+U in my text (take the + sign out)...
| Quote: |
| Also can you post your Compute node code in full (as I want to see how you set up the evironment tree, and myVar etc.) |
The entire file is too big and it has code for many other business cases that don't get used for this case. The relevant piece of code that shows everything I'm doing is as follows:
| Code: |
SET Environment.Variables.ivCdxSecurity[] = PASSTHR(instrumentSqlStmt, secId, relatedSecId, secId, relatedSecId);
SET Environment.Variables.ivCdxPosition[] = PASSTHR(positionSqlStmt, secId, relatedSecId, secId, relatedSecId);
SET Environment.Variables.cdsInstrIds[] =
(SELECT I.INSTRUMENT_ID FROM Environment.Variables.ivCdxSecurity[] AS I WHERE I.INSTRUMENT_TYP = 610);
DECLARE myId CHARACTER;
FOR instrId AS Environment.Variables.cdsInstrIds[] DO
--SET myId = '0610-99DT006W-52108H7K';
--SET myId = instrId;
SET myId = CAST(instrId AS CHARACTER);
SET Environment.Variables.instrId = instrId;
SET Environment.Variables.myId = myId;
SET Environment.Variables.ivPosition[] =
(SELECT P FROM Environment.Variables.ivCdxPosition[] AS P WHERE P.PARENT_INSTRUMENT_ID = myId);
-- do the business logic for what we have in EV.ivPosition[]
END FOR; |
| Quote: |
| In addition if the Envrionment is loaded from a message, can you post a sample message as well. |
The Environment is loaded from a couple of PASSTHRU's that execute queries in an Oracle database.
So, in summary:
1. I am now able to select all the columns that I want using the "SELECT P from Environment.Variables.ivCdxPosition[] AS P ..." construct
2. for the life of my I can't get the bind variable thing to work. The only way I ever got what I expected assigned to ivPosition[] is if I uncomment the line "SET myId = '0610-99DT006W-52108H7K';". I can't understand why myId doesn't get any value assigned to it from the FOR loop. |
|
| Back to top |
|
|
| ltopa |
| Posted: Mon Mar 12, 2007 4:24 pm Post subject: SELECT function to select message parts in compute node |
|
|
Newbie
Joined: 07 Mar 2007
Posts: 7
|
I think I got it... It was my FOR loop: looking at the ${Environment} trace, I see that instrId is still a list. If I change my assignment for myId to something like this:
| Code: |
| SET myId = instrId.INSTRUMENT_ID; |
what do you know? it works! 
Now my last question is: is there an easier way to set up the FOR loop, so that instrId is a simple character string and not a list? |
|
| Back to top |
|
|
| mgk |
| Posted: Sat Mar 24, 2007 1:38 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi.
There is no way in your case to make the FOR instrId point to a simple character variable, as the character variable is not the repeating element.
The statement:
| Code: |
| FOR instrId AS Environment.Variables.cdsInstrIds[] DO ... |
makes the instrId point to each cdsInstrIds element in turn. So to access the INSTRUMENT_ID element underneath each cdsInstrIds repeat you must use the instrId.INSTRUMENT_ID syntax. If however, your repeat was the INSTRUMENT_ID element itself (that is there were many INSTRUMENT_ID elements under the each csdInstrIds) then a statement such as
| Code: |
| FOR instrId AS Environment.Variables.cdsInstrIds.INSTRUMENT_ID[] |
would indeed make the instrId point to a character element. However, given your message structure it is not possible in this case.
Is it really a problem to use the instrId.INSTRUMENT_ID syntax?
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 |
|
|
| ltopa |
| Posted: Mon Mar 26, 2007 5:42 am Post subject: |
|
|
Newbie
Joined: 07 Mar 2007
Posts: 7
|
mgk: thanks for your reply. I'm using inStr.INSTRUMENT_ID and it works just fine.
Back to another question I had in my original post. Is there a way to use the SELECT function with the IN clause where the list of id's is dynamically built? In other words, this works:
| Code: |
| SET Environment.Variables.ivSecurity[] = (SELECT I FROM Environment.Variables.ivCdxSecurity[] AS I WHERE I.INST_ID IN (1, 2, 3, 4)); |
But how can I have (1, 2, 3, 4) come from a variable? (an array variable?)
Also, mgk wrote that you can have nested SELECTs: could you give me a few examples of how that would be used? |
|
| Back to top |
|
|
|
|
