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 Index » WebSphere Message Broker (ACE) Support » ESQL Select DISTINCT

Post new topic  Reply to topic Goto page 1, 2  Next
 ESQL Select DISTINCT « View previous topic :: View next topic » 
Author Message
murdeep
PostPosted: Thu Oct 22, 2009 1:12 pm    Post subject: ESQL Select DISTINCT Reply with quote

Master

Joined: 03 Nov 2004
Posts: 211

Hello, I have the task of parsing a message that contains repeating records. This is no problem. Once it is parsed I need to break the data out into three subsets. My issue is the original records are a join of three tables so when I parse it out into the three subsets there will be duplicates.

My question is: is there an simple ESQL stmt like the SQL SELECT DISTINCT that can execute against a tree to help me remove duplicates? Or do I have to code it by navigating the tree and doing compares and deleting the data from the tree?

Thanks
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Thu Oct 22, 2009 4:32 pm    Post subject: Re: ESQL Select DISTINCT Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY

murdeep wrote:
Hello, I have the task of parsing a message that contains repeating records. This is no problem. Once it is parsed I need to break the data out into three subsets. My issue is the original records are a join of three tables so when I parse it out into the three subsets there will be duplicates.

My question is: is there an simple ESQL stmt like the SQL SELECT DISTINCT that can execute against a tree to help me remove duplicates? Or do I have to code it by navigating the tree and doing compares and deleting the data from the tree?

Thanks

Have you looked at the meaning of "ITEM" and "THE" in ESQL?
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
murdeep
PostPosted: Mon Oct 26, 2009 12:18 pm    Post subject: Reply with quote

Master

Joined: 03 Nov 2004
Posts: 211

Don't see how ITEM and THE will help in this case. I can use them to get a tree built but I don't see how they help remove duplicates. For example, in my case I have the following input message...

Code:
<cars>
 <carInfo>
  <make>FORD</make>
  <model>PINTO</model>
 </carInfo>
 <carInfo>
  <make>FORD</make>
  <model>MUSTANG</model>
 </carInfo>
 <carInfo>
  <make>FORD</make>
  <model>F150</model>
 </carInfo>
 <carInfo>
  <make>SCION</make>
  <model>xB</model>
 </carInfo>
</cars>


So in my case need all makes but duplicates removed.

Code:
<cars>
 <make>FORD></make>
 <make>SCION</make>
<cars>


not (as with the case using ITEM)

Code:
<cars>
 <make>FORD></make>
 <make>FORD></make>
 <make>FORD></make>
 <make>SCION</make>
<cars>


Does this make it more clear?
Back to top
View user's profile Send private message
mqjeff
PostPosted: Mon Oct 26, 2009 1:06 pm    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

No, ESQL Select doesn't do this for you.

Nor does it do ORDER BY or GROUP BY.
Back to top
View user's profile Send private message
Aldrine
PostPosted: Wed Aug 17, 2011 4:10 am    Post subject: Solution for getting Distinct Reply with quote

Novice

Joined: 25 Jul 2011
Posts: 22
Location: India

Hi,

Code:
DECLARE tempGroup ROW;
DECLARE index INTEGER 1;
set tempGroup.Field[1] = InputRoot.XMLNS.Body.Field[1];

FOR loopField As InputRoot.XMLNS.Body.Field[*]
IF NOT EXISTS SELECT Field FROM temprow.Field[] WHERE tempGroup.Field = loopField
set tempGroup.field[index++] = loopField
END IF
END FOR 

This way we can get only the unique values.
_________________
--
Aldrine Einsteen


Last edited by Aldrine on Wed Aug 17, 2011 5:18 am; edited 1 time in total
Back to top
View user's profile Send private message
mqjeff
PostPosted: Wed Aug 17, 2011 4:20 am    Post subject: Re: Solution for getting Distinct Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

Aldrine wrote:
Hi,

Code:
DECLARE tempGroup ROW;
DECLARE index INTEGER 1;
set tempGroup.Field[1] = InputRoot.XMLNS.Body.Field[1];

FOR loopField As InputRoot.XMLNS.Body.Field[*]
IF NOT EXISTS SELECT Field FROM temprow.Field[] WHERE temprow.Field = loopField
set temprow.field[index++] = loopField
END IF
END FOR 

This way we can get only the unique values.


This is a terribly inefficient implementation.

You should be using reference variables for both pointers into the tree.

You should be using XMLNSC not XMLNS.

You're also confusing "tempGroup" and "temprow'.
Back to top
View user's profile Send private message
Aldrine
PostPosted: Wed Aug 17, 2011 5:22 am    Post subject: Reply with quote

Novice

Joined: 25 Jul 2011
Posts: 22
Location: India

Thank you for pointing out the tempVariable switch; i have corrected them.

As of efficiency, I was pointing at the way of doing it. but making it efficient is as per need and utility of the End User
_________________
--
Aldrine Einsteen
Back to top
View user's profile Send private message
simon.starkie
PostPosted: Sat Feb 25, 2012 6:21 am    Post subject: Reply with quote

Disciple

Joined: 24 Mar 2002
Posts: 180

...

Last edited by simon.starkie on Sun Feb 26, 2012 9:24 pm; edited 7 times in total
Back to top
View user's profile Send private message
smdavies99
PostPosted: Sat Feb 25, 2012 6:30 am    Post subject: Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

you are free to ask IBM to include this functionality in a future release if you can't manage without those two particular verbs.

As an aside,

What prompted you to re-open a post where the last entry was 6 months ago and the original post was more than 2 years ago?

It is generally regarded as pretty 'bad form' to do this. We prefer a new post with a reference to the old one.
_________________
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
View user's profile Send private message
mqjeff
PostPosted: Sat Feb 25, 2012 7:18 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

simon.starkie wrote:
mqjeff wrote:
No, ESQL Select doesn't do this for you.

Nor does it do ORDER BY or GROUP BY.



It's not real SQL then, is it.


That's why there's an "E" at the front of it.
Back to top
View user's profile Send private message
Vitor
PostPosted: Sat Feb 25, 2012 1:02 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

simon.starkie wrote:
It's not real SQL then, is it.


Who said it was?
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
simon.starkie
PostPosted: Sun Feb 26, 2012 7:47 pm    Post subject: Reply with quote

Disciple

Joined: 24 Mar 2002
Posts: 180

...
Back to top
View user's profile Send private message
arunkumo
PostPosted: Tue Jan 21, 2020 2:39 pm    Post subject: distinct records Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

Can some one tells how to get the distinct record count from esql for message tree. I don't have any database interactions. From the Message tree, i want to find the unique records, i have tryed cardinality, it is not giving the unique records.Is there any way ?
Back to top
View user's profile Send private message
timber
PostPosted: Tue Jan 21, 2020 3:41 pm    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Please open a new thread and refer to this (very old) thread if you want to show that you have done your research.

In your new thread, please supply details about what exactly you want to do (with examples)
Back to top
View user's profile Send private message
arunkumo
PostPosted: Wed Jan 22, 2020 2:41 pm    Post subject: Reply with quote

Novice

Joined: 21 Sep 2014
Posts: 20

I have created new thread like Unique Records.Can anyone know some solution,please let me know.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ESQL Select DISTINCT
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.