| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| need to use SQL query with 'UNION' in ESQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| know_ashu |
 Posted: Thu Aug 18, 2005 11:08 pm Post subject: need to use SQL query with 'UNION' in ESQL |
 |
|
Novice
Joined: 25 Jun 2005
Posts: 22
|
I am using WMQI 2.1 and in control center getting a syntax error while using UNION clause in my SQL Statement:-
---------------------------------------------------------------------
select t1.col1 as c1, t1.col2 as c2 from Database.table1 as t1
UNION
select t2.col1 as c1, t2.col2 as c2 from Database.table2 as t2
---------------------------------------------------------------------
When checked ESQL documentation it doesnt have any UNION clause...now the question is WHAT/HOW to modify my query to achieve the same results. |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Fri Aug 19, 2005 12:12 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi know_ashu,
Why dont you use the PASSTHRU and code the SELECT based on the database syntax.
If you want to stick to MQSI, then try something like this:
select t1.col1 and t2.col1 as c1, t1.col2 and t2.col2 as c2 from Database.table1 as t1,Database.table2 as t2
I dont know if this will work, maybe you can try all options.
Will try too and post of it works.
Regards. |
|
| Back to top |
|
|
| know_ashu |
| Posted: Fri Aug 19, 2005 4:01 am Post subject: |
|
|
Novice
Joined: 25 Jun 2005
Posts: 22
|
Thanks for your response. althought I couldnt solve the problem yet.
I believe select queries I am using are not simple queries.
I need to get the output of the following select statement:
SELECT T13.CR_TC_TYPE AS TandCIdentifier, T04.CREDIT_LOG_NUM AS TandCCreditLogNum, T04.CR_TRANS_TC_STATUS AS TandCStatus.Code, T13.CR_TC_ATTR_DESC AS TandCStatus.ClauseDescription, T04.CHAR_60_VALUE AS TandCStatus.ClauseParameter, T04.CR_TC_ITEM_NUM AS TandCStatus.ItemNum, T04.CR_TC_SEQ_NUM AS TandCStatus.ClauseSequence
FROM Database.Table04 AS T04, Database.Table13 AS T13, Database.Table68 AS T68
WHERE T04.CREDIT_LOG_NUM = CREDIT_LOG_NUM AND T68.REC_TYPE = 'CR' AND T68.SEQ_NUM = 1 AND T68.REQUEST_KEY > (CURRENT_TIMESTAMP - INTERVAL '10' DAY) AND T04.CR_TC_TYPE = T13.CR_TC_TYPE AND T04.CR_TC_ATTR_NUM = T13.CR_TC_ATTR_NUM
UNION
SELECT ' ' AS TandCIdentifier, T04.CREDIT_LOG_NUM AS TandCCreditLogNum, ' ' AS TandCStatus.Code, '----------------------------GENERAL COMMENTS----------------------------' AS TandCStatus.ClauseDescription, ' ' AS TandCStatus.ClauseParameter, 888 AS TandCStatus.ItemNum, 888 AS TandCStatus.ClauseSequence
FROM Database.Table04 AS T04, Database.Table68 AS T68
WHERE T04.CREDIT_LOG_NUM = CREDIT_LOG_NUM AND T68.REC_TYPE = 'CR' AND T68.SEQ_NUM = 1 AND T68.REQUEST_KEY > CURRENT_TIMESTAMP - INTERVAL '10' DAYS
UNION
SELECT T18.MESSAGE_NUM AS TandCIdentifier, T04.CREDIT_LOG_NUM AS TandCCreditLogNum, ' ' AS TandCStatus.Code, T18.MESSAGE_DESC AS TandCStatus.ClauseDescription, ' ' AS TandCStatus.ClauseParameter, 999 AS TandCStatus.ItemNum, 999 AS TandCStatus.ClauseSequence
FROM Database.Table04 AS T04, Database.Table08 AS T08, Database.Table18 AS T18, Database.Table68 AS T68
WHERE T04.CREDIT_LOG_NUM = CREDIT_LOG_NUM AND T68.REC_TYPE = 'CR' AND T68.SEQ_NUM = 1 AND T68.REQUEST_KEY > CURRENT_TIMESTAMP - INTERVAL '10' DAYS AND T04.CREDIT_LOG_NUM = T08.CREDIT_LOG_NUM AND T04.VERS_NUM = T08.VERS_NUM AND T08.MESSAGE_NUM = T18.MESSAGE_NUM AND T18.MRKT_VIEW = 'Y'
ORDER BY 2, 6, 7 DESC) |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Fri Aug 19, 2005 4:12 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi know_ashu,
I tried the query but it does not work...I think PASSTHRU would the way out.
And if you're thinking of querying so many tables at one time, I suggest you try to find a way of seperating the query and then merging and arranging the data. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Aug 19, 2005 4:23 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
ESQL Select does not support UNION, ORDER BY, or GROUP BY (or a bunch of other fairly standard clauses).
You can do a few different things to join the results of those three selects into one result set.
The easiest might be to use the LIST function.
| Code: |
| Set Environment.Variables.Result[] = LIST{Select ..., Select ..., Select ...}; |
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| know_ashu |
| Posted: Mon Aug 29, 2005 4:00 am Post subject: |
|
|
Novice
Joined: 25 Jun 2005
Posts: 22
|
PASSTHRU or LIST...problem is single quotes in the query.
It gives syntax errors in the query while using single quotes as below:
****************************************
DECLARE QUERY CHARACTER;
SET QUERY = 'select t1.a, t2.b, t2.c from user1.table_a as a, user1.table_b as b where a.c = 'abc' and b.d = 'pqr' and a.c1 = ? and b.c1 = ?
Union
Select t3.c, t4.d, ' ' from user1.table_c as c, user1.table_d as d where c.c1 = ? and d.c2 = ?';
SET OutputRoot.XML.Data[] = PASSTHRU(QUERY, Environment.Variable.a, Environment.Variable.b, Environment.Variable.c, Environment.Variable.d);
****************************************
This compute node contains only these above lines...and giving error.
Kindly help... |
|
| Back to top |
|
|
| JT |
| Posted: Mon Aug 29, 2005 5:37 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
Why not go all the way and substitute all of your variables (including the static ones) ?
| Quote: |
SET QUERY = 'select t1.a, t2.b, t2.c from user1.table_a as a, user1.table_b as b where a.c = ? and b.d = ? and a.c1 = ? and b.c1 = ?
Union
Select t3.c, t4.d, ' ' from user1.table_c as c, user1.table_d as d where c.c1 = ? and d.c2 = ?';
SET OutputRoot.XML.Data[] = PASSTHRU(QUERY, 'abc', 'pqr', Environment.Variable.a, Environment.Variable.b, Environment.Variable.c, Environment.Variable.d); |
|
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Aug 29, 2005 7:33 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
have you first checked if the query runs in the db2 command line processor ???
I think the problem is the quotes occuring the the middle of the query...
Try to fix the query in the command line then use it in ESQL. |
|
| Back to top |
|
|
| Ian |
| Posted: Wed Aug 31, 2005 3:54 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
You need to escape the single quotes (with a single quote) in the SQL statement string you are build.
_________________
Regards, Ian |
|
| 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
|
|
|
|
