x

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
more ▼

asked Aug 01, 2012 at 08:40 PM in Default

muk gravatar image

muk
400 31 33 37

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

@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.
more ▼

answered Aug 01, 2012 at 09:35 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1842
x987
x587
x372

asked: Aug 01, 2012 at 08:40 PM

Seen: 931 times

Last Updated: Aug 02, 2012 at 08:38 AM