asked
query is returning the wrong results
Hello everyone, I wrote this query but the only problem is for the business phone in my logic it is supposed to first look at ADR_PHONES for business phone (POS 1 and type 'BUS') and if it is not there, it should get the phone from PSEASON table. for some reason it is still looking only at PSEASON for business phone. Can someone people look at this and verify maybe I have some parentheses wrong or something? Thanks! Select Top (100) Percent
PERSON.ID, PERSON.PREFIX As Prefix, PERSON.FIRST_NAME As [First Name], PERSON.MIDDLE_NAME As [Middle Name], PERSON.LAST_NAME As [Last Name], PERSON.SUFFIX, PERSON.PERSON_STATUS, Max(Case When (PERSON.PERSON_CORP_INDICATOR Is Null And PSEASON.ADDR_TYPE = 'B') Then PERSON1.PREFERRED_NAME Else Null End) As [Company Name], Max(Case When ADDRESS_LS.POS = 1 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As [Address Line 1], Max(Case When ADDRESS_LS.POS = 2 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As [Address Line 2], Max(Case When ADDRESS_LS.POS = 3 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As [Address Line 3], ADDRESS.CITY As City, ADDRESS.STATE As State, ADDRESS.ZIP As Zip, ADDRESS.COUNTRY As Country, PSEASON.ADDR_TYPE As [Address Type], PEOPLE_EMAIL.PERSON_EMAIL_ADDRESSES As [Preferred Email], Max(Case When (PERPHONE.POS = 1 And PERPHONE.PERSONAL_PHONE_TYPE = 'CELL') Then PERPHONE.PERSONAL_PHONE_NUMBER Else (Case When (PERPHONE.POS = 2 And PERPHONE.PERSONAL_PHONE_TYPE = 'CELL') Then PERPHONE.PERSONAL_PHONE_NUMBER End) End) As Cell, Max(Case When (PERPHONE.POS = 1 And PERPHONE.PERSONAL_PHONE_TYPE = 'HOM') Then PERPHONE.PERSONAL_PHONE_NUMBER Else (Case When (PERPHONE.POS = 2 And PERPHONE.PERSONAL_PHONE_TYPE = 'HOM') Then PERPHONE.PERSONAL_PHONE_NUMBER End) End) As Home, Max(Case When (ADR_PHONES.POS = 1 And ADR_PHONES.ADDRESS_PHONE_TYPE Is Not Null And ADR_PHONES.ADDRESS_PHONE_TYPE = 'BUS') Then (ADR_PHONES.ADDRESS_PHONES) Else ((Case When PSEASON.ADDR_LOCAL_PHONE Like '%ü%' Then (RTrim(SubString(PSEASON.ADDR_LOCAL_PHONE, 1, CharIndex('ü', PSEASON.ADDR_LOCAL_PHONE) - 1))) Else PSEASON.ADDR_LOCAL_PHONE End)) End) As [Business Phone], Case When (Case When (Max(Case When (ADR_PHONES.POS = 1 And ADR_PHONES.ADDRESS_PHONE_TYPE Is Not Null) Then ADR_PHONES.ADDRESS_PHONE_EXTENSION Else ADR_PHONES1.ADDRESS_PHONE_EXTENSION End)) Is Null Then PSEASON.ADDR_LOCAL_EXT Else (Max(Case When (ADR_PHONES.POS = 1 And ADR_PHONES.ADDRESS_PHONE_TYPE Is Not Null) Then ADR_PHONES.ADDRESS_PHONE_EXTENSION Else ADR_PHONES1.ADDRESS_PHONE_EXTENSION End)) End) = 'ü' Then Null Else (Case When (Max(Case When (ADR_PHONES.POS = 1 And ADR_PHONES.ADDRESS_PHONE_TYPE Is Not Null) Then ADR_PHONES.ADDRESS_PHONE_EXTENSION Else ADR_PHONES1.ADDRESS_PHONE_EXTENSION End)) Is Null Then PSEASON.ADDR_LOCAL_EXT Else (Max(Case When (ADR_PHONES.POS = 1 And ADR_PHONES.ADDRESS_PHONE_TYPE Is Not Null) Then ADR_PHONES.ADDRESS_PHONE_EXTENSION Else ADR_PHONES1.ADDRESS_PHONE_EXTENSION End)) End) End As [Business Phone EXT], Max(Case When PERSON_LS.POS = 1 And PERSON_LS.REUNION_CLASS Is Not Null Then PERSON_LS.REUNION_CLASS End) As [Reunion Class 1], Max(Case When PERSON_LS.POS = 2 And PERSON_LS.REUNION_CLASS Is Not Null Then PERSON_LS.REUNION_CLASS End) As [Reunion Class 2], Max(Case When PERSON_LS.POS = 3 And PERSON_LS.REUNION_CLASS Is Not Null Then PERSON_LS.REUNION_CLASS End) As [Reunion Class 3], Max(Case When PERSON_LS.POS = 1 And PERSON_LS.MAIL_RULES Is Not Null Then PERSON_LS.MAIL_RULES End) As [Mail Rules 1], Max(Case When PERSON_LS.POS = 2 And PERSON_LS.MAIL_RULES Is Not Null Then PERSON_LS.MAIL_RULES End) As [Mail Rules 2], Max(Case When PERSON_LS.POS = 3 And PERSON_LS.MAIL_RULES Is Not Null Then PERSON_LS.MAIL_RULES End) As [Mail Rules 3] From PERSON Left Outer Join ADDRESS On PERSON.PREFERRED_ADDRESS = ADDRESS.ADDRESS_ID Left Outer Join ADDRESS_LS On ADDRESS_LS.ADDRESS_ID = ADDRESS.ADDRESS_ID Left Outer Join PSEASON On
PSEASON.ID =
PERSON.ID And PSEASON.PERSON_ADDRESSES = ADDRESS.ADDRESS_ID Left Outer Join PERPHONE On
PERPHONE.ID =
PERSON.ID Left Outer Join PERSON As PERSON1 On PERSON.PREF_BUS_ADDRESS = PERSON1.PREFERRED_ADDRESS Full Outer Join PEOPLE_EMAIL On
PEOPLE_EMAIL.ID =
PERSON.ID Full Outer Join ADR_PHONES On PERSON.PREFERRED_ADDRESS = ADR_PHONES.ADDRESS_ID Full Outer Join ADR_PHONES As ADR_PHONES1 On PERSON.PREF_BUS_ADDRESS = ADR_PHONES1.ADDRESS_ID Full Outer Join PERSON_LS On
PERSON_LS.ID =
PERSON.ID Where (PEOPLE_EMAIL.POS = 1 And
PERSON.ID Is Not Null) Or (PEOPLE_EMAIL.POS Is Null And Len(
PERSON.ID) = 7) Group By
PERSON.ID, PERSON.PREFIX, PERSON.FIRST_NAME, PERSON.MIDDLE_NAME, PERSON.LAST_NAME, PERSON.SUFFIX, PERSON.PERSON_STATUS, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.ZIP, ADDRESS.COUNTRY, PSEASON.ADDR_TYPE, PEOPLE_EMAIL.PERSON_EMAIL_ADDRESSES, PSEASON.ADDR_LOCAL_EXT Having
PERSON.ID = '0031856' Order By
PERSON.ID
@muk There is nothing wrong with the logic except the **ADR\_PHONES.ADDRESS\_PHONE\_TYPE Is Not Null** part. If you are looking to meet the **ADR\_PHONES.ADDRESS\_PHONE\_TYPE = 'BUS'** condition that it is kinda implied that it is not null. In other words, you can replace the condition in this part of your case statement to this: case when (ADR_PHONES.POS = 1 and ADR_PHONES.ADDRESS_PHONE_TYPE = 'BUS' then ADR_PHONES.ADDRESS_PHONES else (( case /* etc */)) end You are also missing some else parts, for example, in the Cell part. You have case with when and else, the else has the case with when but without else. Also, it would be probably cheaper to add the
PERSON.ID = '0031856' to the where clause and remove the having clause. This is because the WHERE happens before the grouping and HAVING - after, so it would be cheaper to filter out the desired records prior to grouping them, particularly because the GROUP BY clause is pretty wide in your case.
8 People are following this question.
