June 5, 2012 at 9:33 pm
How do you omit the null values returned inthis query?
SELECT CASE WHEN MONTH(DueDate)=5 THEN DueDate END AS DT
FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]
June 5, 2012 at 10:15 pm
Didn't work.
June 5, 2012 at 10:44 pm
SELECT CASE WHEN MONTH(DueDate)=5 THEN DueDate END AS DT
FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]
where DueDate is not null
June 5, 2012 at 11:12 pm
I'm guessing this is what you want:
;with PodCte
as
(
select
case
when month(DueDate) = 5
then DueDate
end as DL
from Purchasing.PurchaseOrderDetail
)
select *
from PodCte
where DL is not null;
In your immediate query DueDate isn't necessarily NULL, but since MONTH(DueDate) <> 5 that column value is going to be NULL because of your CASE statement. So to get your desired results (I'm guessing that this is your desired results, if not let me know) you need to use a subquery or CTE.
June 5, 2012 at 11:14 pm
Thank you.
June 6, 2012 at 2:30 am
A brilliant example of the importance telling what you want. You (apparently) want the due date where the month is may, which can be retrieved with the following query:
SELECT DueDate AS DT
FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]
WHERE MONTH(DueDate)=5
Much cleaner code in my opinion. If Your query should happen to be for a specific year, like 2012, this query could possibly perform much better, given that there is an index supporting the query:
SELECT DueDate AS DT
FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]
WHERE DueDate >= '2012-05-01' and DueDate < '2012-06-01'
June 7, 2012 at 9:39 am
ReginaR1975 (6/5/2012)
SELECT CASE WHEN MONTH(DueDate)=5 THEN DueDate END AS DTFROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]
where DueDate is not null
I want to clarify something that misled you here, since you're using CASE statements.
See, the CASE statement will return a NULL value for any conditions not accounted for in your WHEN clauses. What this means is that for every date where MONTH(DueDate) <> 5, you got a NULL results.
Now, when you use a WHERE clause directly referencing the DueDate column, the query is going to eliminate any records that have a NULL value in the DueDate column in the table. If you have not modified the data in your AdventureWorks database, you should see 0 records in that table that have DueDate as NULL. The NULLs you were seeing were due to your CASE statement alone.
The other posters are correct in how they suggested you filter the data (instead of using a CASE statement at all), but I wanted to try to clarify the difference between a true NULL value in your source data (the table) and a NULL value that results from a CASE statement without an ELSE clause. Always remember this fact about CASE statements especially if you're using one for a DELETE statement, ever.
June 8, 2012 at 6:19 am
Try this
SELECT CASE WHEN MONTH(DueDate)=5 THEN DueDate END AS DT
FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]
where DueDate is not null and Month(DueDate) is not null
June 8, 2012 at 9:51 am
you may use "having case ... end is not null"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply