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

avatar image

muk
440 33 35 40

(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

avatar image

Oleg
17.2k 3 7 28

(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.

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:

x2091
x1069
x749
x428

asked: Aug 01, 2012 at 08:40 PM

Seen: 1310 times

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

Copyright 2016 Redgate Software. Privacy Policy