Sample Header Ad - 728x90

Sanity Check - Result set different after breaking up case expression in where clause into different selects w/union all

0 votes
0 answers
89 views
I'm back again with more dumb questions. This time I am dealing with refactoring a single select with nested case expressions in the where clause. I'm trying to optimize this, and in attempting to do so, I broke up the query into multiple selects w/ union alls. This does run quite a bit faster, but now I'm seeing more rows return than before, and this does not seem to be due to duplicate rows. There is more to this query, like creating the temp tables, etc. However, as far as I can tell, that has not had an effect on the result set. I have been able to determine that the second select of the refactored query (much of which is omitted because it is repetitive) is pulling in more results than before. So, where did I go wrong when pulling this apart? How does my new query/WHERE clause(s) differ from the original? I super appreciate your help in advance. /*Original*/ select /*some columns*/ from #SmallerRange IPS inner join #LargerRange V on V.AccountNumber !=IPS.AccountNumber and V.InOrOut = IPS.InOrOut and V.LocNum = IPS.LocNum WHERE CASE WHEN IPS.InOrOut = 'I' THEN CASE WHEN IPS.EntryDateTime = V.EntryDateTime AND IPS.ExitDateTime = V.ExitDateTime AND IPS.ProgramID = V.ProgramID THEN 1 WHEN IPS.EntryDateTime = V.EntryDateTime AND IPS.OriginDate=V.OriginDate AND IPS.ProgramID = V.ProgramID THEN 1 WHEN IPS.EntryDateTime = V.EntryDateTime AND IPS.ExitDateTime = V.ExitDateTime AND IPS.OriginDate=V.OriginDate THEN 1 WHEN IPS.ProgramID = V.ProgramID AND IPS.ExitDateTime = V.ExitDateTime AND IPS.OriginDate=V.OriginDate THEN 1 WHEN IPS.Name = V.Name AND IPS.Name 'N/A' AND IPS.ExitDateTime = V.ExitDateTime AND IPS.OriginDate=V.OriginDate THEN 1 ELSE 0 END WHEN IPS.InOrOut = 'O' THEN CASE WHEN IPS.ProgramID = V.ProgramID AND IPS.OriginDate=V.OriginDate AND IPS.DropbyDateTime = V.DropbyDateTime THEN 1 WHEN IPS.ProgramID = V.ProgramID AND IPS.DropbyDateTime = V.DropbyDateTime AND IPS.ARRVLTIME = V.ARRVLTIME THEN 1 WHEN IPS.ProgramID = V.ProgramID AND IPS.OriginDate=V.OriginDate AND IPS.ARRVLTIME = V.ARRVLTIME THEN 1 WHEN IPS.DropbyDateTime = V.DropbyDateTime AND IPS.OriginDate=V.OriginDate AND IPS.ARRVLTIME = V.ARRVLTIME THEN 1 WHEN IPS.DropbyDateTime = V.DropbyDateTime AND IPS.OriginDate=V.OriginDate AND IPS.Name = V.Name THEN 1 ELSE 0 END ELSE 0 END = 1 AND CASE WHEN V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name V.Name THEN 0 WHEN V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name = 'N/A' THEN 0 ELSE 1 END = 1 /*Refactor*/ select /*some columns*/ from #SmallerRange IPS inner join #LargerRange V on V.InOrOut = IPS.InOrOut and V.LocNum = IPS.LocNum and V.AccountNumber != IPS.AccountNumber WHERE IPS.InOrOut = 'I' and IPS.EntryDate = V.EntryDate AND IPS.OriginDate=V.OriginDate AND IPS.ProgramID = V.ProgramID and not (V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name V.Name and IPS.Name = 'N/A') and not (V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name = 'N/A') union all select /*some columns*/ from #SmallerRange IPS inner join #LargerRange V on V.InOrOut = IPS.InOrOut and V.LocNum = IPS.LocNum and V.AccountNumber != IPS.AccountNumber WHERE IPS.InOrOut = 'I' and IPS.EntryDate = V.EntryDate AND IPS.ExitDate = V.ExitDate AND IPS.ProgramID = V.ProgramID and not (V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name V.Name and IPS.Name = 'N/A') and not (V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name = 'N/A')
Asked by scarr030 (115 rep)
Sep 28, 2021, 02:04 PM
Last activity: Sep 28, 2021, 02:33 PM