better way to write union

Question

better way to write union

cindyumemoto

Newbie

Points: 8
More actions
November 3, 2010 at 10:56 am

#240720

Can anyone tell me a better way to write this query, these are both large tables and it is very slow... Any help would be appreciated.. Thank you in advance.
SQL:
select c.ClaimID, Modifier = isnull(c.Modifier1, null), ServiceDOS =c.DOS from @tmpAudit1 t,tblClaimCharge c with (NOLOCK) where t.ClaimID = c.ClaimID and c.Modifier1 is not null
union
select c.ClaimID, Modifier = isnull(c.Modifier2, null), ServiceDOS =c.DOS from @tmpAudit1 t,tblClaimCharge c with (NOLOCK) where t.ClaimID = c.ClaimID and c.Modifier2 is not null
union
select c.ClaimID, Modifier = isnull(c.Modifier3, null), ServiceDOS =c.DOS from @tmpAudit1 t,tblClaimCharge c with (NOLOCK) where t.ClaimID = c.ClaimID and c.Modifier3 is not null
union
select c.ClaimID, Modifier = isnull(c.Modifier4, null), ServiceDOS =c.DOS from @tmpAudit1 t,tblClaimCharge c with (NOLOCK) where t.ClaimID = c.ClaimID and c.Modifier4 is not null

Viewing 6 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

It looks like it could all be one query, without unions at all, if you were to use either nested IsNull, or (better yet) Coalesce.

SELECT

c.ClaimID,

COALESCE(c.Modifier1, c.Modifier2, c.Modifier3, c.Modifier4) AS Modifier,

c.DOS AS ServiceDOS

FROM

@tmpAudit1 AS t

INNER JOIN tblClaimCharge AS c

ON t.ClaimID = c.ClaimID

WHERE

c.Modifier1 IS NOT NULL

OR c.Modifier2 IS NOT NULL

OR c.Modifier3 IS NOT NULL

OR c.Modifier4 IS NOT NULL ;

I formatted it for readability, updated the join to the current standard, and used Coalesce. Does it do what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

cindyumemoto Newbie Points: 8 More actions · Answer 2

You rock! This worked great and it knocked the scan count down substainsially! Thank you

GSquared SSC Guru Points: 260824 More actions · Answer 3

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeffrey Williams SSC Guru Points: 90351 More actions · Answer 4

GSquared, your solution is not quite the same as the original unions. With the unions - if modifier1 and modifier2 both have values - and those values are different there would be two rows. In your solution, the second row for modifier2 would be eliminated.

And, if all modifier fields have values - instead of the expected 4 rows for the claim, your solution would only have a single row.

I don't think this can be done without the unions - but it depends on what the OP is actually looking for in the results. For efficiency, I would change the UNION to a UNION ALL - guessing that you would want a row for each modifier and that you cannot have the same modifier multiple times on a single claim.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

GSquared SSC Guru Points: 260824 More actions · Answer 5

True. I made some assumptions about use that may not be valid.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon