Avatar of pzozulka
pzozulka
 asked on

SQL Case in WHERE clause

Need help with the case statement below. Why is it not working, and what can be done to make it work:

My goal is to return records where QuoteLastModifiedUser is IN (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteLastModifiedUser)

If QuoteLastModifiedUser  is either NULL or Empty String, then return the record if QuoteCreator  is IN (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteCreator)

SELECT         cq.QuoteCreator,
        case when cq.QuoteCreator in (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteCreator) then 'T'
             else 'F' 
        end as QuoteCreatedByFullUser,
        cq.QuoteLastModifiedUser
             
        FROM  
        CustomerQuote cq
        INNER JOIN Entity a ON (cq.AgentId=a.PartyId) 
        LEFT OUTER JOIN	CustomerAccount c ON (cq.QuoteNumber = c.QuoteNumber)
        LEFT OUTER JOIN Entity s ON (cq.QuoteOwnerId=s.PartyId) 
        LEFT JOIN Entity sp ON s.[SalespersonId] = sp.[PartyId]
        
        WHERE cq.QuoteCreateDate between '10/25/2001' AND '10/25/2017'   And (cq.QuoteTypeId = '1' Or cq.QuoteTypeId = '3') 

		AND CASE WHEN cq.QuoteLastModifiedUser IS NOT NULL
					THEN cq.QuoteLastModifiedUser IN (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteLastModifiedUser)
			END
			     
        order by cq.QuoteNumber

Open in new window

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

another way (using left joins) which I am going to recommend
SELECT
      cq.QuoteCreator
    , CASE
            WHEN creator.FullName IS NOT NULL
                  THEN 'T'
            ELSE 'F'
      END AS QuoteCreatedByFullUser
    , cq.QuoteLastModifiedUser
FROM CustomerQuote cq
      INNER JOIN Entity a
            ON (cq.AgentId = a.PartyId)
      LEFT OUTER JOIN CustomerAccount c
            ON (cq.QuoteNumber = c.QuoteNumber)
      LEFT OUTER JOIN Entity s
            ON (cq.QuoteOwnerId = s.PartyId)
      LEFT JOIN Entity sp
            ON s.[SalespersonId] = sp.[PartyId]
      LEFT JOIN UserAccount AS creator
            ON cq.QuoteCreator = creator.FullName
      LEFT JOIN UserAccount AS modifier
            ON cq.QuoteLastModifiedUser  = modifier.FullName
WHERE cq.QuoteCreateDate BETWEEN '10/25/2001' AND '10/25/2017'
      AND cq.QuoteTypeId IN ('1','3')
      AND (
              modifier.FullName IS NOT NULL
          OR (cq.QuoteLastModifiedUser IS NULL OR cq.QuoteLastModifiedUser = '')
          )
ORDER BY
      cq.QuoteNumber

Open in new window

{+edits, sorry} and lastly:
note I have introduced IN('1','3') , this is a great way to use IN()- I was much less keen on the way you were trying to use IN()  originally.
pzozulka

ASKER
I ran this, and am not getting the results I was looking for. The problem is that QuoteLastModifiedUser contains empty strings, and so does the UserAccount.FullName. Joining these two is resulting in rows being return with ambiguous data. For example, if QuoteLastModifiedUser  was John Smith, it would be more certain that that the UserAccount.FullName was also John Smith, and the JOIN would work successfully, and more or less uniquely. But in this situation, there are records that are being joined incorrectly.

Regardless, my main concern here is about the CASE in the WHERE clause from my original post.

How do I make sure that if QuoteLastModifiedUser  is empty string, then return a different column? This is what I'm trying to achieve, but it's not working:
        
WHERE
IF cq.QuoteLastModifiedUser IS NOT NULL THEN
   AND cq.QuoteLastModifiedUser IN 
      (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteLastModifiedUser)
ELSE
   AND cq.QuoteCreator in 
      (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteCreator)

Open in new window

ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes