| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| ESQL subselects |
« View previous topic :: View next topic » |
| Author |
Message
|
| MrSmith |
 Posted: Tue Dec 22, 2009 3:18 am Post subject: ESQL subselects |
 |
|
Master
Joined: 20 Mar 2008
Posts: 215
|
I have had a quick scan on the forums for examples of subselects and also scanned through the manuals but there seems few examples of using subselects in ESQL, so my question is 1) are they not very "reliable" within ESQL or 2) cause poor performance or both?
I want to be able to select an element within a group of a group of elements and was hoping not to have to use nested FOR loops.
Example:
| Code: |
<outer_group>
<inner_group>
<error_code>24</error_code>
</inner_group>
<inner_group>
<error_code>26</error_code>
</inner_group>
<inner_group>
<error_code>27</error_code>
</inner_group>
</outer_group>
<outer_group>
<inner_group>
<error_code>24</error_code>
</inner_group>
<inner_group>
<error_code>26</error_code>
</inner_group>
<inner_group>
<error_code>27</error_code>
</inner_group>
</outer_group>
<outer_group>
<inner_group>
<error_code>24</error_code>
</inner_group>
<inner_group>
<error_code>26</error_code>
</inner_group>
<inner_group>
<error_code>27</error_code>
</inner_group>
</outer_group>
<outer_group>
<inner_group>
<error_code>24</error_code>
</inner_group>
<inner_group>
<error_code>26</error_code>
</inner_group>
<inner_group>
<error_code>27</error_code>
</inner_group>
</outer_group>
|
Kind of thing whereby I want to select a count of all the errorvalues of value 24 which i would expect to return of 4.
If anybody can give me some pointers of a proven ESQL statement that would achieve this, if it is of course possible, then would be much appreciated. I have tried some of my own but hit problems with deployment errors regarding scalr vlaues and not allowed [] in middle of statement kind of thing.
Version is 6.0 on solaris.
Please do not tell of future version sthat may dio this it is not an option.
Thanks |
|
| Back to top |
|
 |
| mgk |
| Posted: Tue Dec 22, 2009 3:05 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello, yes ESQL does support subselects. There are examples under the "Transforming a complex message" (topic ak05750) and they should be reliable and perform (depending on how you write the select). For the example you posted, if you add a "Root" tag around your message to make it valid XML, then the following ESQL will produce a count of 4 which you were looking for:
| Code: |
| SET OutputRoot.XMLNSC.Top.Count = SELECT COUNT( * ) FROM InputRoot.XMLNSC.Root.outer_group[] AS B WHERE EXISTS(SELECT C.* FROM B.inner_group[] AS C WHERE C.error_code = 24 ); |
I hope this helps,
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 |
|
|
| mqjeff |
| Posted: Wed Dec 23, 2009 5:46 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Is the subselect strictly necessary here? Can't you count inner_group where inner_group.error_code = 24?
i.e.
| Code: |
| SET OutputRoot.XMLNSC.Top.Count = SELECT COUNT( B.inner_group ) FROM InputRoot.XMLNSC.Root.outer_group[] AS B WHERE b.inner_group.error_code = 24; |
? |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Dec 23, 2009 6:22 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
In this one specific case you can but really you would normally have to go through all inner_groups for each outer_group and your code only looks at the first inner_group.error_code, and ignores the others (e.g. if you were looking for 27 instead of 24 it would always fail).
Anyway, Merry Christmas Jeff 
_________________
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 |
|
|
| Pegazus |
| Posted: Wed Feb 09, 2011 10:01 pm Post subject: ESQL Sub Select |
|
|
Novice
Joined: 10 Aug 2006
Posts: 16
|
I have a similar situation and I am confused as to how to select the values
Input XML
| Code: |
<B:Fee>
<B:FeeType>Other</B:FeeType>
<B:FeeAmount>89.00</B:FeeAmount>
</B:Fee>
<B:Fee>
<B:FeeType>Other</B:FeeType>
<B:FeeAmount>99.00</B:FeeAmount>
</B:Fee>
<B:Fee>
<B:FeeType>Other</B:FeeType>
<B:FeeAmount>109.00</B:FeeAmount>
</B:Fee>
|
If 1st Occurance of FeeType = 'Other' I need to get FeeAmount and move it to variable A
If 2nd Occurance of FeeType = 'Other' I need to get FeeAmount and move it to variable B
If 3rd Occurance of FeeType = 'Other' I need to get FeeAmount and move it to variable C
Any help is much appreciated |
|
| 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
|
|
|
|
