| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Select Distinct Alternatives? |
« View previous topic :: View next topic » |
| Author |
Message
|
| jfrankman |
 Posted: Tue Jul 21, 2009 6:49 am Post subject: Select Distinct Alternatives? |
 |
|
Apprentice
Joined: 18 Jun 2009
Posts: 36
|
Since the message broker does not support the distinct keyword, I need to find a way to eliminate duplication. I have the following statement:
| Code: |
SET OutputRoot.XMLNSC.ns3:Policy.ns3:PolicyLocations.ns3:PolicyLocation[I].ns3:PropertyOwners[]=
(
SELECT E.LIENID AS ns3:PropertyOwner.ns3:ID, F.SEARCH AS ns3:PropertyOwner.ns3:Lienholder.ns3:Name, H.LOCATIONID as ns3:PropertyOwner.ns3:Lienholder.ns3:MailLocation.ns3:ID, H.STREET as ns3:PropertyOwner.ns3:Lienholder.ns3:MailLocation.ns3:AddressLine1, H.CITY || ', ' || H.STATE || ' ' || H.ZIP5 as ns3:PropertyOwner.ns3:Lienholder.ns3:MailLocation.ns3:AddressLine2
FROM Database.FBNEXUS.FBPOLICY as A ,
Database.FBNEXUS.FBSECTION1 as B ,
Database.FBNEXUS.FBPROPERTY as C ,
Database.FBNEXUS.FBLINEITEMLIENS as D,
Database.FBNEXUS.FBLIENS as E,
Database.FBNEXUS.FBCLIENT as F ,
Database.FBNEXUS.FBCLNTLOC as G,
Database.FBNEXUS.FBLOCATION as H
WHERE C.LOCATIONID = locationID AND A.FBROOT='069658' AND A.COMPANY='01' and A.POLICYID = B.POLICYID and B.SECT1ID = C.SECT1ID and C.PROPERTYID = D.PROPERTYID and D.LIENID = E.LIENID and E.CLIENTID = F.CLIENTID and F.CLIENTID = G.CLIENTID AND G.LOCTYPEID=1 and G.LOCATIONID= H.LOCATIONID
); |
The problem is that my select statement may produce duplicate rows. Is there a way to only select rows that are unique?
I could write another loop that deletes the rows from the message, but I would prefer it if I did not have to do this.
I did come across this tidbit in the documentation, but can't understand how it would work:
"ITEM selections .... In conjunction with a ROW constructor, it can be used to create a SELECT query that collapses levels of repetition in the input message. " |
|
| Back to top |
|
 |
| jbanoop |
| Posted: Tue Jul 21, 2009 6:56 am Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
| what database are you using ? Did you have a look at the PASSTHRU statement ? |
|
| Back to top |
|
|
| jfrankman |
| Posted: Tue Jul 21, 2009 7:07 am Post subject: |
|
|
Apprentice
Joined: 18 Jun 2009
Posts: 36
|
I am using DB2.
I did look at the passthru statement, but I am not sure how to give the results the proper field names. For instance in my ESQL I set a field as 'AS ns3:PropertyOwner.ns3:ID'. This is not a valid SQL alias name as it is usually too long and contains special reserved characters. If I did use the passthru command how do I set the alias names in ESQL? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Jul 21, 2009 7:13 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Assign the results of passthrough to a temporary tree, and then reselect from that into your final output. |
|
| 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
|
|
|
|
