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

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial