| Author |
Message
|
| murdeep |
 Posted: Thu Oct 22, 2009 1:12 pm Post subject: ESQL Select DISTINCT |
 |
|
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 |
|
 |
| fjb_saper |
| Posted: Thu Oct 22, 2009 4:32 pm Post subject: Re: ESQL Select DISTINCT |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
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 |
|
|
| murdeep |
| Posted: Mon Oct 26, 2009 12:18 pm Post subject: |
|
|
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 |
|
|
| mqjeff |
| Posted: Mon Oct 26, 2009 1:06 pm Post subject: |
|
|
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 |
|
|
| Aldrine |
| Posted: Wed Aug 17, 2011 4:10 am Post subject: Solution for getting Distinct |
|
|
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 |
|
|
| mqjeff |
| Posted: Wed Aug 17, 2011 4:20 am Post subject: Re: Solution for getting Distinct |
|
|
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 |
|
|
| Aldrine |
| Posted: Wed Aug 17, 2011 5:22 am Post subject: |
|
|
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 |
|
|
| simon.starkie |
| Posted: Sat Feb 25, 2012 6:21 am Post subject: |
|
|
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 |
|
|
| smdavies99 |
| Posted: Sat Feb 25, 2012 6:30 am Post subject: |
|
|
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 |
|
|
| mqjeff |
| Posted: Sat Feb 25, 2012 7:18 am Post subject: |
|
|
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 |
|
|
| Vitor |
| Posted: Sat Feb 25, 2012 1:02 pm Post subject: |
|
|
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 |
|
|
| simon.starkie |
| Posted: Sun Feb 26, 2012 7:47 pm Post subject: |
|
|
Disciple
Joined: 24 Mar 2002
Posts: 180
|
|
| Back to top |
|
|
| arunkumo |
| Posted: Tue Jan 21, 2020 2:39 pm Post subject: distinct records |
|
|
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 |
|
|
| timber |
| Posted: Tue Jan 21, 2020 3:41 pm Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
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 |
|
|
| arunkumo |
| Posted: Wed Jan 22, 2020 2:41 pm Post subject: |
|
|
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 |
|
|
|
|
