| Author |
Message
|
| lancelotlinc |
 Posted: Wed Jan 25, 2012 1:10 pm Post subject: SELECT statement correlation name not valid |
 |
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Hi -
SOAPInput -> Compute -> SOAPReply
WMB 7.0.0.2 Ifix 2
| Code: |
SET OutputLocalEnvironment.XMLNSC.Data.SelectResult.Row[] =
PASSTHRU ('SELECT indtable.USER FROM IND.TXN
AS indtable WHERE indtable.TXN_ID = ?'
VALUES ( CAST( InputRoot.SOAP.Body.TransactionId AS DECIMAL )));
SET user = OutputLocalEnvironment.XMLNSC.Data.SelectResult.Row[1];
|
When deploying successfully compiled flow to EG:
| Quote: |
| Correlation name 'TXN_ID' not valid. |
How to escape the TXN_ID so that it passes through?
Control-space on 'indtable.' shows a list with TXN_ID in it.
Alternately, also tried the direct SELECT statement with same result (ie. not passthru).
Can someone please suggest to me things to try so that the Bar will deploy to the EG without the error ?
| Quote: |
Event Type: Error
Event Source: WebSphere Broker v7002
Event Category: None
Event ID: 2432
Date: 1/25/2012
Time: 2:06:29 PM
User: N/A
Computer: <systemname>
Description:
( MB7_xxx.xx_EG06 ) ('com.company.middleware.flows.<project>.xxx_subflow_Compute1.Main', '110.11') : The correlation name ''TXN_ID'' is not valid. Those in scope are: 'xxx, yyyy, etc etc etc ... Environment, InputLocalEnvironment, OutputLocalEnvironment, InputRoot, InputBody, InputProperties, OutputRoot, InputExceptionList, OutputExceptionList, InputDestinationList, OutputDestinationList, zyx'.
The first element of a field reference must be a valid correlation name, from those in scope. This message may sometimes be due to an incorrectly formed or spelled expression which is not intended to be a field reference being parsed as if it were a field reference because the parser does not recognize it.
Correct the syntax of your ESQL expression in node ''com.<company>.middleware.flows.project.xxx_subflow_Compute1.Main'', around line and column ''110.11'', then redeploy the message flow.
Data:
0000: 31 00 32 00 30 00 30 00 1.2.0.0.
|
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Jan 25, 2012 1:15 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So you're using a PASSTHRU, which means you need to use a valid SQL select statement, rather than a valid ESQL select statement.
So try
| Code: |
SET OutputLocalEnvironment.XMLNSC.Data.SelectResult.Row[] =
PASSTHRU ('SELECT USER FROM IND.TXN
WHERE TXN_ID = ?'
VALUES ( CAST( InputRoot.SOAP.Body.TransactionId AS DECIMAL ))); |
|
|
| Back to top |
|
|
| adubya |
| Posted: Wed Jan 25, 2012 1:20 pm Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| The OP's SQL looked valid to me. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jan 25, 2012 1:29 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Cut & pasted your code verbatim and still same error. I need to be able to escape TXN_ID. For some reason, the runtime is not liking that database column name.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jan 25, 2012 1:35 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| Cut & pasted your code verbatim and still same error. I need to be able to escape TXN_ID. For some reason, the runtime is not liking that database column name. |
ESQL will accept double-quotes inside of single quotes as quotable text.
So
| Code: |
SET OutputLocalEnvironment.XMLNSC.Data.SelectResult.Row[] =
PASSTHRU ('SELECT USER FROM IND.TXN
WHERE "TXN_ID" = ?'
VALUES ( CAST( InputRoot.SOAP.Body.TransactionId AS DECIMAL ))); |
|
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jan 25, 2012 1:44 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| adubya |
| Posted: Wed Jan 25, 2012 1:49 pm Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
As a test, if you change the SQL to use a different column than TXN_ID in the WHERE clause (and a compatible VALUE entry) then does it work ?
Trying to confirm if it's definitely the "TXN_ID" bit. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Jan 25, 2012 1:55 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
Hi.
Look at the syntax diagram for PASSTHRU again in the info center. You have mixed up two different "styles" of PASSTHRU
The two key valid types for this case are:
PASSTHRU 'expression' VALUES ( 'xxx' );
and
PASSTHRU ( 'expression' , 'xxx' );
The first type is a "newer" form which allows the DSN to be speficied as well (with a TO clause), and the second is an "older" type which does not and is more like a function than a statement.
However, you have a mix of these styles which is invalid.
Kind 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 |
|
|
| adubya |
| Posted: Wed Jan 25, 2012 1:59 pm Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jan 25, 2012 3:04 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Thanks for the expert help quick !
The BIP2087E was caused by a different (E)SQL statement elsewhere in the file which, when fixed, the error went away. I had touched that previous statement yesterday and moved on to this piece of code and spent alot of today on the code I posted. Both statements use the TXN_ID, and when adubya asked to change it, the TXN_ID error still occured, which led me to the earlier statement.
Silly rabbit, Trix are for Kids.
Thanks again !!
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
|
|
