Case Statement in WHERE clause
-
I am trying to put a case statement into a query to "reverse" an assignment done in a previous query. My initial query does a sum on invoice amounts by date and company/division. When there are Account numbers in the Invoice table but not in the AccountInfo table, I still need to display the data, so we assign the Company Name/Division to "Not Assigned" (instead of passing a NULL):
SELECT SUM(A.Invoice), A.InvoiceDate,
CASE WHEN C.CompanyName IS NULL THEN 'Not Assigned'
ELSE C.CompanyName
END AS CompanyName
FROM Invoices AS A
LEFT OUTER JOIN AccountInfo AS C
ON A.AcctNo = C.AcctNo
Where A.InvoiceDate BETWEEN '1/1/07' AND '2/1/07'
GROUP BY A.InvoiceDate, C.CompanyName
ORDER BY A.InvoiceDate, C.CompanyName
The reporting tool I am using has a drilldown feature that will allow the report user to select a CompanyName to drilldown to get more details. For instance, one CompanyName/Division may have multiple AcctNo(s), so the reporting tool passes the CompanyName ("Not Assigned") into the next query. Obviously, "Not Assigned" does not exist in the real DB, so I need to convert it back to "NULL" for the query to work. Here is what I am trying:
-- Setup Variables to simulate passed report passed variable
DECLARE @CoName varchar(30)
SET @CoName = 'Not Assigned'
---
SELECT SUM(A.Invoice), A.InvoiceDate,
CASE WHEN C.CompanyName IS NULL THEN 'Not Assigned'
ELSE C.CompanyName
END AS CompanyName,
A.AcctNo
FROM Invoices AS A
LEFT OUTER JOIN AccountInfo AS C
ON A.AcctNo = C.AcctNo
Where A.InvoiceDate BETWEEN '1/1/07' AND '2/1/07'
AND
-- CompanyName IS NULL -- This grabs data!
CASE
WHEN (@CoName = 'Not Assigned') THEN (CompanyName IS NULL)
ELSE (CompanyName = @CoName)
END
GROUP BY A.InvoiceDate, A.AcctNo, C.CompanyName
ORDER BY A.InvoiceDate, A.AcctNo, C.CompanyName
Any suggestions?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
Try this, but it may not be the most efficent way of doing it:
DECLARE @CoName varchar(30)
SET @CoName = 'Not Assigned'
---
SELECT SUM(A.Invoice), A.InvoiceDate,
CASE WHEN C.CompanyName IS NULL THEN 'Not Assigned'
ELSE C.CompanyName
END AS CompanyName,
A.AcctNo
FROM Invoices AS A
LEFT OUTER JOIN AccountInfo AS C
ON A.AcctNo = C.AcctNo
Where
A.InvoiceDate BETWEEN '1/1/07' AND '2/1/07'
AND
coalesce(C.CompanyName,@CoName) = @CoName
-- -- CompanyName IS NULL -- This grabs data!
-- CASE
-- WHEN (@CoName = 'Not Assigned') THEN (CompanyName IS NULL)
-- ELSE (CompanyName = @CoName)
-- END
GROUP BY A.InvoiceDate, A.AcctNo, C.CompanyName
ORDER BY A.InvoiceDate, A.AcctNo, C.CompanyName
-
-
Could not figure out a way to use the CASE statement in the where clause (thanks for all those who contributed), so I decided a little coding was in order (if - else, hardcode CompanyName IS NULL, CompanyName = @CoName):
DECLARE @CoName varchar(30)
SET @CoName = 'Not Assigned'
---
IF @CoName = 'Not Assigned'
BEGIN
SELECT SUM(A.Invoice), A.InvoiceDate,
CASE WHEN C.CompanyName IS NULL THEN 'Not Assigned'
ELSE C.CompanyName
END AS CompanyName,
A.AcctNo
FROM Invoices AS A
LEFT OUTER JOIN AccountInfo AS C
ON A.AcctNo = C.AcctNo
Where A.InvoiceDate BETWEEN '1/1/07' AND '2/1/07'
AND
CompanyName IS NULL
GROUP BY A.InvoiceDate, A.AcctNo, C.CompanyName
ORDER BY A.InvoiceDate, A.AcctNo, C.CompanyName
END
ELSE
BEGIN
... Repeat above, just set CompanyName = @CoName in where clause...
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
SELECT SUM(A.Invoice), A.InvoiceDate,
CASE WHEN C.CompanyName IS NULL THEN 'Not Assigned'
ELSE C.CompanyName
END AS CompanyName,
A.AcctNo
FROM Invoices AS A
LEFT OUTER JOIN AccountInfo AS C
ON A.AcctNo = C.AcctNo
Where
A.InvoiceDate BETWEEN '1/1/07' AND '2/1/07'
AND coalesce(CompanyName, @CoName) = @CoName
GROUP BY A.InvoiceDate, A.AcctNo, C.CompanyName
ORDER BY A.InvoiceDate, A.AcctNo, C.CompanyName
Should work just as well as you solution. If CompanyName is null, it compares @CoName = @CoName (true).
-
SELECT SUM(A.Invoice), A.InvoiceDate,
CASE WHEN C.CompanyName IS NULL THEN 'Not Assigned'
ELSE C.CompanyName
END AS CompanyName,
A.AcctNo
FROM Invoices AS A
LEFT OUTER JOIN AccountInfo AS C
ON A.AcctNo = C.AcctNo
Where
A.InvoiceDate BETWEEN '1/1/07' AND '2/1/07'
AND (C.CompanyName is null or C.CompanyName = @CoName)
GROUP BY A.InvoiceDate, A.AcctNo, C.CompanyName
ORDER BY A.InvoiceDate, A.AcctNo, C.CompanyName
Or you can use this also.
-
Thanks for everyone's responses again! I think there might be a misunderstanding... In the initial query, I am converting any "CompanyNames" from NULL to "Not Assigned".
If the initial CompanyName was NULL, the variable is being passed by the reporting tool (my simulated @CoName variable) to the new query as "Not Assigned". However, in the 2nd query, I need to convert BACK from "Not Assigned" (@CoName), if that is the selected CompanyName in the report, to NULL to capture the data in the database in the WHERE clause.
I am not passing a NULL value into the WHERE clause.... if @CoName is "Not Assigned" it needs to go back to NULL.
WHERE Clause:
(if) @CoName = 'Not Assigned' -> C.CompanyName IS NULL
(if) @CoName = Anything else -> C.CompanyName = @CoName
Thanks again!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
SELECT SUM(A.Invoice), A.InvoiceDate,
CASE WHEN C.CompanyName IS NULL THEN 'Not Assigned'
ELSE C.CompanyName
END AS CompanyName,
A.AcctNo
FROM Invoices AS A
LEFT OUTER JOIN AccountInfo AS C
ON A.AcctNo = C.AcctNo
Where
A.InvoiceDate BETWEEN '1/1/07' AND '2/1/07'
AND ((C.CompanyName is null and @CoName = 'Not Assigned') or C.CompanyName = @CoName)
GROUP BY A.InvoiceDate, A.AcctNo, C.CompanyName
ORDER BY A.InvoiceDate, A.AcctNo, C.CompanyName
One more slight modification to the query, and this should give you what you are looking for.
-
WOOO HOOOO! It worked! Much better than "brute force"! Just goes to show that collaboration is much better than going it alone!
I can say, if not for this website, I do not think I would be the DBA I am today....
Thanks Lynn!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply