ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportImplementing Select Distinct or Group by in ESQL

Post new topicReply to topic
Implementing Select Distinct or Group by in ESQL View previous topic :: View next topic
Author Message
bhavya.work
PostPosted: Wed Feb 24, 2016 1:02 pm Post subject: Implementing Select Distinct or Group by in ESQL Reply with quote

Newbie

Joined: 07 Feb 2016
Posts: 3

First Post, sorry if I break any forum rules.

Goal : Split messages with distinct values. In example below x/xtypes/xtype/xtypeName is the distinct and group by element

Input Mesage:
Code:

<x>
    <xtypes>
        <xtype>
            <xtypeID>1</xtypeID>
            <xtypeName>John</xtypeName>
            <xtypeValue>value1</xtypeValue>
            <xtypeMoreValue>mv1</xtypeMoreValue>
        </xtype>
        <xtype>
            <xtypeID>2</xtypeID>
            <xtypeName>Bob</xtypeName>
            <xtypeValue>value2</xtypeValue>
            <xtypeMoreValue>mv2</xtypeMoreValue>
        </xtype>
        <xtype>
            <xtypeID>3</xtypeID>
            <xtypeName>John</xtypeName>
            <xtypeValue>value3</xtypeValue>
            <xtypeMoreValue>mv3</xtypeMoreValue>
        </xtype>
        <xtype>
            <xtypeID>4</xtypeID>
            <xtypeName>Michael</xtypeName>
            <xtypeValue>value4</xtypeValue>
            <xtypeMoreValue>mv4</xtypeMoreValue>
        </xtype>
    </xtypes>
</x>


Propagate three messages as follows
First:
Code:
<x>
    <xtypes>
        <xtype>
            <xtypeID>1</xtypeID>
            <xtypeName>John</xtypeName>
            <xtypeValue>value1</xtypeValue>
            <xtypeMoreValue>mv1</xtypeMoreValue>
        </xtype>
          <xtype>
            <xtypeID>3</xtypeID>
            <xtypeName>John</xtypeName>
            <xtypeValue>value3</xtypeValue>
            <xtypeMoreValue>mv3</xtypeMoreValue>
        </xtype>
    </xtypes>
</x>
       

Second:
Code:
  <x>
    <xtypes>
     <xtype>
            <xtypeID>2</xtypeID>
            <xtypeName>Bob</xtypeName>
            <xtypeValue>value2</xtypeValue>
            <xtypeMoreValue>mv2</xtypeMoreValue>
        </xtype>
    </xtypes>
</x>

Third:

Code:

<x>
    <xtypes>
        <xtype>
            <xtypeID>4</xtypeID>
            <xtypeName>Michael</xtypeName>
            <xtypeValue>value4</xtypeValue>
            <xtypeMoreValue>mv4</xtypeMoreValue>
        </xtype>
    </xtypes>
</x>



My approach Algorithm :

Using Distinct : Find distinct elements in the xpath above .
Loop on xtypes.type if curLoopName = forLoopName then copy xtype tree to output root.

So thoughts on how to do this? I tried some other approaches but nothing worth sharing.

I tried putting a link for one of the implementation I found on this forun but forum rules doesn't allow posting link for new users.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Wed Feb 24, 2016 1:09 pm Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17448

You can't do SELECT DISTINCT in ESQL Select, as far as I remember.

There is an ESQL implementation of quicksort somewhere. You could sort your tree by x/xtypes/xtype/xtypeName and then loop over it again and propagate as you find differences.

Or you could create a tree in the XMLNSC domain in Environment or Local Environment, and use the x/xtypes/xtype/xtypeName as the top level element of the tree... something like

set NEXT CHILD OF OutputLocalEnvironment.XMLNSC.DistinctNames.{InputRoot.XMLNSC.x.xtypes.xtype.xtypeName} = curRef

for some reference curRef looping over the input tree.


Then you would have elements like OutputLocalEnvrionment.XMLNSC.DistinctNames.John with the xtypes for John under it.

Then loop over the DistinctNames tree and build your output tree from that.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
bhavya.work
PostPosted: Wed Feb 24, 2016 1:24 pm Post subject: Reply with quote

Newbie

Joined: 07 Feb 2016
Posts: 3

You remember right. There is no group by and distinct. It is clearly mentioned in documentation.

I think the approach of creating distinctName tree in env will probably work. Will have to try out. Just two problems I foresee with this approach though.

1) the actual input message is very large. This is just one xtypes section. there is ytpes,ztypes etc.. which need to be copied. I guess, I can work around it somehow.
2) the xtypeName can be blank sometimes and that is a valid message as well. Will have to work around that too.. somehow.

Will try and report if success.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Wed Feb 24, 2016 1:35 pm Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17448

The approach with using DistinctNames is more or less treating the env tree as a hash structure. As long as all of the "" are supposed to be in the same message, this will still work (except you might need to test to see if it's blank and then use a placebo value "blank" or etc.)

If all of your xname,yname,zname stuff is under the same child, you can just copy the whole child tree.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
timber
PostPosted: Wed Feb 24, 2016 2:28 pm Post subject: Reply with quote

Yatiri

Joined: 25 Aug 2015
Posts: 699

Quote:
First Post, sorry if I break any forum rules.
Well, actually you did, and sincere thanks for breaking at least two rules that most people follow. You actually cared about the person trying to understand your problem, and you explained the problem fully at the first attempt.

Your problem is tricky because you don't know the full set of xtypes until the entire message has been parsed. So you need to hold the entire (potentially huge) message tree in memory at the same time.
I might be tempted to experiment with a Collector node. It will store all of the messages on a queue ( not in memory ) until the collections are complete or the Control terminal is triggered. The correlator would obviously be the xtypeName. Worth a try, maybe?
Back to top
View user's profile Send private message
bhavya.work
PostPosted: Wed Feb 24, 2016 9:28 pm Post subject: Reply with quote

Newbie

Joined: 07 Feb 2016
Posts: 3

timber wrote:
Quote:
First Post, sorry if I break any forum rules.
Well, actually you did, and sincere thanks for breaking at least two rules that most people follow. You actually cared about the person trying to understand your problem, and you explained the problem fully at the first attempt.


Hehe, Yeah first post but I am one of the biggest forum lurkers out there. So, I think from the perspective of the end user and know what to write to get an answer.

Just a quick update I got it to work with some version of what jeff suggested. I am still refining it to optimize for my need. Once done I promise I will come around and post the solution for some other soul who may struggle with this. Too busy right now.

Timber, some day i will try playing with collectors I have never played with them before and can't risk doing POC too much in my plate right now.

Thanks both of you.
Back to top
View user's profile Send private message
maurito
PostPosted: Thu Feb 25, 2016 1:30 am Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

bhavya.work wrote:
timber wrote:
Quote:
First Post, sorry if I break any forum rules.
Well, actually you did, and sincere thanks for breaking at least two rules that most people follow. You actually cared about the person trying to understand your problem, and you explained the problem fully at the first attempt.


Hehe, Yeah first post but I am one of the biggest forum lurkers out there. So, I think from the perspective of the end user and know what to write to get an answer.

Just a quick update I got it to work with some version of what jeff suggested. I am still refining it to optimize for my need. Once done I promise I will come around and post the solution for some other soul who may struggle with this. Too busy right now.

Timber, some day i will try playing with collectors I have never played with them before and can't risk doing POC too much in my plate right now.

Thanks both of you.

If I were you I would follow timber's advice and let the collector node do all the hard work for you. At the end of the collector node you would receive one collected message for each xtypeName, containing the relevant sub trees for that xtypeName.
Quote:

Timber, some day i will try playing with collectors I have never played with them before and can't risk doing POC too much in my plate right now.

This is a good reason for using collector now. You will learn a very useful technique and it will save you lots of time and hassle.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Thu Feb 25, 2016 5:59 am Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17448

The difficulty with using the collector node is that it is asychronous and could take a much larger time to create the output messages.

On the other hand, it will be more efficient in memory use, and more obvious in the flow what's going on.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
krish418
PostPosted: Sat Aug 19, 2017 6:37 am Post subject: Reply with quote

Newbie

Joined: 19 Aug 2017
Posts: 1

Bhavya,

I am in a same situation. can you please post your code for finding distinct vales and grouping the xml. Thank you
Back to top
View user's profile Send private message
mqjeff
PostPosted: Sat Aug 19, 2017 6:58 am Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17448

krish418 wrote:
Bhavya,

I am in a same situation. can you please post your code for finding distinct vales and grouping the xml. Thank you


ESQL Select does not support ORDER or GROUP by or DISTINCT.

Read the rest of this thread for other suggestions.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
paintpot
PostPosted: Mon Aug 21, 2017 5:19 am Post subject: Reply with quote

Centurion

Joined: 19 Sep 2005
Posts: 112
Location: UK

Try using PASSTHRU for more complicated SQL:

"The main use of the PASSTHRU function is to issue complex SELECTs, not currently supported by the integration node, to databases. (Examples of complex SELECTs not currently supported by the integration node are those containing GROUP BY or HAVING clauses.)"
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportImplementing Select Distinct or Group by in ESQL
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.