0

I have a massive query string that I am trying to pick apart for a project I am working on. I am looking for some guidance on how to take it apart(format it into human readable). Also, what are the plus signs? BTW: I tested it and it works.

SELECT  Name ,
        Company ,
        ID ,
        Type = CASE Person.IsClient
                 WHEN NULL THEN 'Other'
                 WHEN '' THEN 'Other'
                 WHEN 'Contact' THEN 'Other'
                 WHEN 'True' THEN 'Client'
                 ELSE ISNULL(Person.IsClient, 'Other')
               END ,
        SortOrder = CASE ISNULL(CompanyIndividual, 'nullstr')
                      WHEN 'company' THEN ISNULL(ClientName, Name)
                      ELSE LTRIM(ISNULL(LastName, '') + ISNULL(FirstName, '')
                                 + ISNULL(Company, Name) + ISNULL(Name, ''))
                    END ,
        SubType ,
        UDF1 ,
        UDF2 ,
        UDF3 ,
        UDF4 ,
        UDF5 ,
        UDF6 ,
        UDF7 ,
        UDF8 ,
        UDF9 ,
        UDF10 ,
        SuperGroup ,
        Notes ,
        WebSite ,
        UserName ,
        LastNoteDateX = CASE ISNULL(UserName, '')
                          WHEN '' THEN LastNoteDate
                          ELSE ( SELECT MAX(LastNoteDate)
                                 FROM   PersonOrganization WITH ( NOLOCK )
                                 WHERE  Person = Person.ID
                                        AND Organization = '04202000000000000066'
                               )
                        END ,
        LastNotePersonX = CASE ISNULL(UserName, '')
                            WHEN '' THEN LastNotePerson
                            ELSE ( SELECT   MAX(LastNotePerson)
                                   FROM     PersonOrganization WITH ( NOLOCK )
                                   WHERE    Person = Person.ID
                                            AND Organization = '04202000000000000066'
                                 )
                          END ,
        ItemType = ISNULL(PersonUnread.ItemType, '') ,
        Flag = ISNULL(Flag.Flag, 'Grey') ,
        PopupReminderPerson = ISNULL(PopupReminder.Person, 'F') ,
        Person.FirstName ,
        Person.LastName ,
        Person.CompanyIndividual ,
        PersonUnreadContactItemType = ISNULL(PersonUnreadContact.ItemType, '') ,
        -1 ,
        Description ,
        Person.ShortName ,
        PhoneNumber = ISNULL(( SELECT   PhoneNumber
                               FROM     Phone WITH ( NOLOCK )
                                        JOIN PersonPhone WITH ( NOLOCK ) ON PersonPhone.Phone = Phone.ID
                               WHERE    PersonPhone.Person = Person.ID
                                        AND Phone.DisplayOrder = 1
                             ), '') ,
        PersonView
FROM    Person WITH ( NOLOCK )
        LEFT JOIN PersonUnread WITH ( NOLOCK ) ON Person.ID = PersonUnread.Ref
                                                  AND PersonUnread.Person = '38808000000000001177'
                                                  AND PersonUnread.ItemType = 'ContactNote'
        LEFT JOIN Flag WITH ( NOLOCK ) ON Person.ID = Flag.Ref
                                          AND Flag.Person = '38808000000000001177'
        LEFT JOIN PopupReminder WITH ( NOLOCK ) ON Person.ID = PopupReminder.Ref
                                                   AND PopupReminder.Person = '38808000000000001177'
                                                   AND PopupReminder.Status = 'Pending'
        LEFT JOIN PersonUnread PersonUnreadContact WITH ( NOLOCK ) ON Person.ID = PersonUnreadContact.Ref
                                                              AND PersonUnreadContact.ItemType = 'Contact'
                                                              AND PersonUnreadContact.Person = '38808000000000001177'
WHERE   ( ( ISNULL(Person.PersonView, 'A') = 'A'
            OR Person.PersonView = ''
          )
          OR ( ISNULL(Person.PersonView, 'A') = 'C'
               AND ( Person.Creator = '38808000000000001177'
                     OR ISNULL(Person.Creator, '') = ''
                   )
             )
          OR ( ISNULL(Person.PersonView, 'A') = 'P'
               AND ( ( Person.Creator = '38808000000000001177'
                       OR ISNULL(Person.Creator, '') = ''
                     )
                     OR EXISTS ( SELECT Assignee
                                 FROM   PersonAssignee WITH ( NOLOCK )
                                 WHERE  PersonAssignee.Person = Person.ID
                                        AND PersonAssignee.Assignee = '38808000000000001177' )
                   )
             )
        )
        AND ( (( Person.SuperGroup = '04202000000000000066'
                 OR Person.ID IN (
                 SELECT Person
                 FROM   PersonOrganization WITH ( NOLOCK )
                 WHERE  Organization = '04202000000000000066' )
               )
              AND EXISTS ( SELECT   Person
                           FROM     PersonOrganization
                           WHERE    Person = '38808000000000001177'
                                    AND Organization = '04202000000000000066'
                                    AND ( ISNULL(RestrictContacts, 'False') <> 'True'
                                          OR EXISTS ( SELECT  Person
                                                      FROM    PersonAssignee
                                                              WITH ( NOLOCK )
                                                      WHERE   Person = Person.ID
                                                              AND Assignee = '38808000000000001177' )
                                          OR Person.ID = '38808000000000001177'
                                          OR Person.Creator = '38808000000000001177'
                                        ) ))
            )
        AND ( Person.SuperGroup = '04202000000000000066'
              OR Person.ID IN ( SELECT  Person
                                FROM    PersonOrganization WITH ( NOLOCK )
                                WHERE   Organization = '04202000000000000066' )
            )
        AND ( ( ISNULL(Person.Name, '') + ' ' + ISNULL(Person.Company, '')
                + ' ' + ISNULL(Person.FirstName, '') + ' '
                + ISNULL(Person.LastName, '') + ' ' + ISNULL(Person.MiddleName,
                                                             '') + ' '
                + ISNULL(Person.Title, '') + ' ' + ISNULL(Person.Prefix, '')
                + ' ' + ISNULL(Person.SubType, '') + ' '
                + ISNULL(Person.WebSite, '') + ' ' + ISNULL(Person.Suffix, '')
                + ' ' + ISNULL(Person.UDF1, '') + ISNULL(Person.UDF2, '')
                + ISNULL(Person.UDF3, '') + ISNULL(Person.UDF4, '')
                + ISNULL(Person.UDF5, '') + ISNULL(Person.UDF6, '')
                + ISNULL(Person.UDF7, '') + ISNULL(Person.UDF8, '')
                + ISNULL(Person.UDF9, '') + ISNULL(Person.UDF10, '') LIKE '%divorce%' )
              OR ( ISNULL(Description, '') LIKE '%divorce%' )
              OR EXISTS ( SELECT    ID
                          FROM      Note WITH ( NOLOCK )
                          WHERE     Note.Item = Person.ID
                                    AND SuperGroup = '04202000000000000066'
                                    AND ( ( Note.Title LIKE '%divorce%' )
                                          OR ( Note.Note LIKE '%divorce%' )
                                        ) )
              OR EXISTS ( SELECT    ID
                          FROM      Phone WITH ( NOLOCK )
                                    JOIN PersonPhone WITH ( NOLOCK ) ON PersonPhone.Phone = Phone.ID
                          WHERE     PersonPhone.Person = Person.ID
                                    AND ( Phone.PhoneNumber LIKE '%divorce%' ) )
              OR EXISTS ( SELECT    ID
                          FROM      Address WITH ( NOLOCK )
                                    JOIN PersonAddress WITH ( NOLOCK ) ON PersonAddress.Address = Address.ID
                          WHERE     PersonAddress.Person = Person.ID
                                    AND ( ( Description LIKE '%divorce%' )
                                          OR ( Street LIKE '%divorce%' )
                                          OR ( City LIKE '%divorce%' )
                                          OR ( State LIKE '%divorce%' )
                                          OR ( Zip LIKE '%divorce%' )
                                          OR ( Country LIKE '%divorce%' )
                                        ) )
              OR EXISTS ( SELECT    ID
                          FROM      Email WITH ( NOLOCK )
                          WHERE     Email.Person = Person.ID
                                    AND ( Email.Email LIKE '%divorce%' ) )
              OR EXISTS ( SELECT    FCGroup.ID
                          FROM      FCGroup WITH ( NOLOCK )
                                    JOIN FCGroupPerson WITH ( NOLOCK ) ON FCGroup.ID = FCGroupPerson.FCGroup
                          WHERE     FCGroupPerson.Person = Person.ID
                                    AND ( FCGroup.Name LIKE '%divorce%' ) )
              OR EXISTS ( SELECT    ID
                          FROM      Note WITH ( NOLOCK )
                          WHERE     Note.Item = Person.ID
                                    AND ( ( Note.Title LIKE '%divorce%' )
                                          OR ( Note.Note LIKE '%divorce%' )
                                        ) )
              OR EXISTS ( SELECT    P2.ID
                          FROM      Person P2 WITH ( NOLOCK )
                                    JOIN PersonAssignee WITH ( NOLOCK ) ON P2.ID = PersonAssignee.Assignee
                          WHERE     PersonAssignee.Person = Person.ID
                                    AND ( P2.Name + ' ' + P2.FirstName + ' '
                                          + P2.MiddleName + ' ' + P2.LastName
                                          + ' ' + P2.Title + ' ' + P2.Company LIKE '%divorce%' ) )
            )
ORDER BY 5
4

1 Answer 1

2

As the comments have pointed out there are multiple tools available to format the query and some suggestions.

Also There are multiple suggestions for improving the query beyond just making it "human readable".

Explaining the "+" operator. The + signs are string concatenation in this case, e.g. SELECT 'a' + 'b' yields 'ab'.

This answer is mostly pulled from the comments with some additions. Thank you to: @ypercube, @spaghettidba, @Kin, @gpatterson

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.