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)
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.