| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| ESQL subselect on database tables |
« View previous topic :: View next topic » |
| Author |
Message
|
| j.f.sorge |
 Posted: Thu Mar 10, 2011 8:33 am Post subject: ESQL subselect on database tables |
 |
|
Master
Joined: 27 Feb 2008
Posts: 218
|
I'v tried four different versions to implement a ESQL subselect which works only on database tables. Only the last, a PASSTHRU, worked fine.
| Code: |
DECLARE rowResult ROW;
DECLARE refResult REFERENCE TO rowResult;
SET rowResult.Result[] = (
SELECT T90.*
FROM Database.{STD_getDbSchemaMandant(Environment)}.MBSVD90T AS T90
WHERE T90.GLT_VON<=CURRENT_TIMESTAMP
AND T90.GLT_BIS>=CURRENT_TIMESTAMP
AND T90.BEARB_KEZ='V'
AND T90.RULE_ID NOT IN (
SELECT ITEM T91.RULE_ID
FROM Database.{STD_getDbSchemaMandant(Environment)}.MBSVD91T AS T91
)
); |
failed with BIP2467E.
| Code: |
DECLARE rowResult ROW;
DECLARE refResult REFERENCE TO rowResult;
SET rowResult.Result[] = (
SELECT T90.*
FROM Database.{STD_getDbSchemaMandant(Environment)}.MBSVD90T AS T90
WHERE T90.GLT_VON<=CURRENT_TIMESTAMP
AND T90.GLT_BIS>=CURRENT_TIMESTAMP
AND T90.BEARB_KEZ='V'
AND NOT EXISTS (
SELECT T91.*
FROM Database.{STD_getDbSchemaMandant(Environment)}.MBSVD91T AS T91
WHERE T91.RULE_ID=T90.RULE_ID
)
); |
failed with BIP2110E.
| Code: |
DECLARE listRuleIds ROW;
SET listRuleIds.Result[] = (
SELECT T91.RULE_ID
FROM Database.{STD_getDbSchemaMandant(Environment)}.MBSVD91T AS T91
);
DECLARE rowResult ROW;
DECLARE refResult REFERENCE TO rowResult;
SET rowResult.Result[] = (
SELECT T90.*
FROM Database.{STD_getDbSchemaMandant(Environment)}.MBSVD90T AS T90
WHERE T90.GLT_VON<=CURRENT_TIMESTAMP
AND T90.GLT_BIS>=CURRENT_TIMESTAMP
AND T90.BEARB_KEZ='V'
AND NOT EXISTS (
SELECT T91.RULE_ID
FROM listRuleIds.Result[] AS T91
WHERE T91.RULE_ID=T90.RULE_ID
)
); |
failed with BIP2111E.
Only
| Code: |
DECLARE rowResult ROW;
DECLARE refResult REFERENCE TO rowResult;
DECLARE chStatement CHARACTER 'SELECT T90.*
FROM ' || STD_getDbSchemaMandant(Environment) || '.MBSVD90T AS T90
WHERE T90.GLT_VON<=CURRENT_TIMESTAMP
AND T90.GLT_BIS>=CURRENT_TIMESTAMP
AND T90.BEARB_KEZ=''V''
AND NOT EXISTS (
SELECT T91.RULE_ID
FROM ' || STD_getDbSchemaMandant(Environment) || '.MBSVD91T AS T91
WHERE T91.RULE_ID=T90.RULE_ID
)
';
SET rowResult.Result[] = PASSTHRU(chStatement); |
worked correctly.
Does anybody have an idea why subselect within ESQL with data from database tables does not work as excepted?
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Thu Mar 10, 2011 8:48 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The BIP2467 means that the internal select is not returning a LIST result, or that the datatype of T90.RULE_ID is not the same as the datatype of T91.RULE_ID.
Perhaps a cast would improve that.
The BIP2110 means you should probably open a PMR.
Likewise with the BIP2111.
Unless, of course, there was significantly more information available on your system that you have failed to mention here that would provide more justification for the BIP2110 and BIP2111 - perhaps additional error messages or messages reported in usertrace. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 10, 2011 8:53 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Is it possible you could add solidDB into your architecture? This different approach would mean a huge increase in performance for you in the queries you've provided.
Alternately, you might try to reconstruct your queries into stored procs and have the processing occur on the database side rather than the WMB side.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| j.f.sorge |
| Posted: Fri Mar 11, 2011 12:02 am Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
| mqjeff wrote: |
The BIP2467 means that the internal select is not returning a LIST result, or that the datatype of T90.RULE_ID is not the same as the datatype of T91.RULE_ID.
Perhaps a cast would improve that. |
T90.RULE_ID and T91.RULE_ID are both of the same type (they are foreign keys within the database tables) but the subselect may return more than one value. How to do the correct CAST?
| mqjeff wrote: |
The BIP2110 means you should probably open a PMR.
Likewise with the BIP2111.
Unless, of course, there was significantly more information available on your system that you have failed to mention here that would provide more justification for the BIP2110 and BIP2111 - perhaps additional error messages or messages reported in usertrace. |
There were some more information within the ExceptionList, so probably user trace contains more, too. So I will open a PMR for that.
| lancelotlinc wrote: |
| Is it possible you could add solidDB into your architecture? This different approach would mean a huge increase in performance for you in the queries you've provided. |
These queries won't be called often only in some special cases where one table does not contain additional data within the other table. So I don't think it will increase performance that much.
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| 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
|
|
|
|
