| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| PASSTHRU SQL ERROR |
« View previous topic :: View next topic » |
| Author |
Message
|
| bobbee |
 Posted: Sun Sep 25, 2016 5:21 am Post subject: PASSTHRU SQL ERROR |
 |
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
I have the following SQL in a PASSTHRU. I have been looking at it for a long time and cannot find the error. Would 'seem' there is a missing PAREN. I deleted ALL but PARENS and they all match. I retyped it completely so I cold eliminate the cut/past conversion issue. I cannot see what is wrong. I moved it around thinking there was something else in the ESQL and this was picking up the error. Nop. there is another big SQL PASSTHRU next to this one. When I remove this one the whole thing deploys. This SQL runs in Data Studio.
| Code: |
PASSTHRU('MERGE INTO CFEXTENSION.XT_ANALYSIS_RESULTS_OBC_TEMP AS OBC
USING
(SELECT TXN.TRANSACTION_ID, INNER.ACCOUNT_ID, INNER.FIRSTSERIALNUMBER, INNER.LASTSERIALNUMBER
FROM (SELECT one.ACCOUNT_ID,
MIN(TTP.VALUE) AS FIRSTSERIALNUMBER,
MAX(TTP.VALUE) AS LASTSERIALNUMBER
FROM CFACCT.TXN_ACCOUNT_REFERENCE AS one
LEFT OUTER KOIN CFACCT.T_TRANSACTION_PROPERTY AS TTP
ON one.TRANSACTION_ID = TTP.TRANSACTION_ID
WHERE on.ACCOUNT_ID in
(SELECT DISTINCT three.ACCOUNT_ID
FROM CFEXTENSION.XT_ANALYSIS_RESULTS_OBC_TEMP AS one,
CFFACT.T_TRANSACTION_RECORDS AS one1,
CFFACT.TXN_ACCOUNT_REFERENCE AS two,
CFFACT.T_ACCOUNT AS three,
CFFACT.T_PTODUCT AS four,
CFFACT.CHANNEL AS five
WHERE one1.TRANSACTION_ID = one.TRANSACTION_ID and
one.TRANSACTION_ID = two.TRANSACTION_ID and
DATE(one.SUBMISSION_TIMESTAMP) BETWEEN ? and ? and
two.ACCOUNT_ID = three.ACCOUNT_ID and
three.PRODUCT_ID = four.PRODUCT_ID and
four.NAME in (?)
) and
TTP.TXN_PROPERTY_TYPE_ID = ?
grouped by ACCOUNT_ID
ordered by ACCOUNT_ID
) AS INNER
LEFT OUTER JOIN CFFACT.TXN_ACCOUNT_REFERENCE AS TXN
ON TXN.ACCOUNT_ID = BB.ACCOUNT_ID
) AS BB
ON OBC.TRANSACTION_ID = BB.TRANSACTION_ID
WHEN MATCHED THEN
UPDATE
order by ACCOUNT_ID
) AS INNER
LEFT OUTER JOIN CFFACT.TXN_ACCOUNT_REFERENCE AS TXN
ON TXN.ACCOUNT_ID = INNER.ACCOUNT_ID
) AS BB
ON OBC.TRANSACTION_ID = BB.TRANSACTION_ID
WHEN MATCHED THEN
UPDATE SET OBC.FIRSTSERIALNUMBER = BB.FIRSTSERIALNUMBER,
OBC.LASTSERIALNUMBER = BB.LASTSERIALNUMBER;'
VALUES(DATE_START, DATE_END, '5', 10015));
|
ERROR
BIP2401E: (.CF_USCHECK_OBC_Main_Controller.Main, 54.20) : Syntax error : expected ')' but found 'keyword Values'.
The expected token was not found.
Correct the syntax of your ESQL expression in node '.CF_USCHECK_OBC_Main_Controller.Main', around line and column '54.20', then redeploy the message flow. |
|
| Back to top |
|
 |
| bobbee |
| Posted: Sun Sep 25, 2016 5:40 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
I had an error in the previous posted code. I was retyping the whole think to remove consideration it was a cut/paste code page error. this is the correct SQL and still gets the error.
| Code: |
PASSTHRU('MERGE INTO CFEXTENSION.XT_ANALYSIS_RESULTS_OBC_TEMP AS OBC
USING
(SELECT TXN.TRANSACTION_ID, INNER.ACCOUNT_ID, INNER.FIRSTSERIALNUMBER, INNER.LASTSERIALNUMBER
FROM (SELECT one.ACCOUNT_ID,
MIN(TTP.VALUE) AS FIRSTSERIALNUMBER,
MAX(TTP.VALUE) AS LASTSERIALNUMBER
FROM CFACCT.TXN_ACCOUNT_REFERENCE AS one
LEFT OUTER KOIN CFACCT.T_TRANSACTION_PROPERTY AS TTP
ON one.TRANSACTION_ID = TTP.TRANSACTION_ID
WHERE on.ACCOUNT_ID in
(SELECT DISTINCT three.ACCOUNT_ID
FROM CFEXTENSION.XT_ANALYSIS_RESULTS_OBC_TEMP AS one,
CFFACT.T_TRANSACTION_RECORDS AS one1,
CFFACT.TXN_ACCOUNT_REFERENCE AS two,
CFFACT.T_ACCOUNT AS three,
CFFACT.T_PTODUCT AS four,
CFFACT.CHANNEL AS five
WHERE one1.TRANSACTION_ID = one.TRANSACTION_ID and
one.TRANSACTION_ID = two.TRANSACTION_ID and
DATE(one.SUBMISSION_TIMESTAMP) BETWEEN ? and ? and
two.ACCOUNT_ID = three.ACCOUNT_ID and
three.PRODUCT_ID = four.PRODUCT_ID and
four.NAME in (?)
) and
TTP.TXN_PROPERTY_TYPE_ID = ?
grouped by ACCOUNT_ID
ordered by ACCOUNT_ID
) AS INNER
LEFT OUTER JOIN CFFACT.TXN_ACCOUNT_REFERENCE AS TXN
ON TXN.ACCOUNT_ID = BB.ACCOUNT_ID
) AS BB
ON OBC.TRANSACTION_ID = BB.TRANSACTION_ID
WHEN MATCHED THEN
UPDATE SET OBC.FIRSTSERIALNUMBER = BB.FIRSTSERIALNUMBER,
OBC.LASTSERIALNUMBER = BB.LASTSERIALNUMBER;'
VALUES (DATE_START, DATE_END, '5', 10015));
|
|
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Sep 25, 2016 6:10 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I'd take a user trace and copy the string to SQLStudio (or whatever tool you use)
I generally create a string in a variable and use that in the PASSTHRU statement.
For complicated commands like this one I build it up bit by bit.
You can see the bits in usertrace. The issue may well become obvious then.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| bobbee |
| Posted: Sun Sep 25, 2016 6:43 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
So, all the things I did to try to get around the issue must have fixed the original issue. But during the problem determination i have commented out DATE_END. I was looking at examples on line and i saw you did not have to wrap the ESQL in PARENS. I removed them. As soon as I did it, flagged DATE_END as being not in scope, ie DEFINED. I had commented it out because it had PARENs in the def and i was trying to determine where it was seeing the missing paren. I uncommeted out DAT_END used in the query and I was able to deploy. THANKS!!!!!!!!!!!!!!!!
Maybe I should go to church now!!!!!!! LOL |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
