We help IT Professionals succeed at work.

SQL Case in WHERE clause

337 Views
Last Modified: 2014-05-29
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

Comment
Watch Question

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

Commented:
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

EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.