Help with the sql

Question

Help with the sql

varunkum

Default port

Points: 1419
More actions
January 31, 2011 at 3:01 pm

#244508

I want to take off the repeating DISPLAY_NAME 's, i only want the distinct one's. Someone please help me change the sql above. Thank you.
select
distinct b.display_name
, (b.STREET_ADDRESS_LINE || ' '|| b.CITY_NAME || ' '|| b.ZIP_PLUS4)
, b.ACCOUNT from dsiproc.trb_vw_subs_info b
right join
(
select distinct display_name, (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) addr from dsiproc.trb_vw_subs_info
where (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) in
(
select (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) from dsiproc.trb_vw_subs_info
group by (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4)
having count(STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) >1
)
group by display_name, (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4)
) z
on (b.STREET_ADDRESS_LINE || ' '|| b.CITY_NAME || ' '|| b.ZIP_PLUS4) = z.addr
order by (b.STREET_ADDRESS_LINE || ' '|| b.CITY_NAME || ' '|| b.ZIP_PLUS4)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 1

You have distinct, but that applies to the entire row, not just the display name. The account differences are resulting in the extra rows. If you removed a row, how should the server decide which account is shown?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

varunkum Default port Points: 1419 More actions · Answer 2

Steve Jones - SSC Editor (2/1/2011)
You have distinct, but that applies to the entire row, not just the display name. The account differences are resulting in the extra rows. If you removed a row, how should the server decide which account is shown?

Thank you now i understand. My code is actually right.

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 3

varunkum (2/2/2011)
Thank you now i understand. My code is actually right.

Indeed but, report may be showing there is a procedural issue in some place in the organization - business has to figure it out why people has more than one account, may be that's the way it is supposed to be but only business can tell. 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

alexsmth114 SSC-Addicted Points: 458 More actions · Answer 4

alexsmth114

SSC-Addicted

Points: 458

March 4, 2011 at 11:41 pm

#1294963

Nice stuff, thanks for sharing!!..

Tech Blog