omit null values

  • 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]

  • ...

    where YourCol is not null

    YourCol would be the column that if it is null you want to omit those rows from the result set.



    Twitter: @SQLife
    Email: sqlsalt(at)outlook(dot)com

  • Didn't work.

  • ReginaR1975 (6/5/2012)


    Didn't work.

    Ok, can you post your whole query with your attempt?



    Twitter: @SQLife
    Email: sqlsalt(at)outlook(dot)com

  • SELECT CASE WHEN MONTH(DueDate)=5 THEN DueDate END AS DT

    FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]

    where DueDate is not null

  • 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.



    Twitter: @SQLife
    Email: sqlsalt(at)outlook(dot)com

  • Thank you.

  • You're welcome. 🙂



    Twitter: @SQLife
    Email: sqlsalt(at)outlook(dot)com

  • 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'



    Ole Kristian VelstadbrĂĄten BangĂĄs - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • ReginaR1975 (6/5/2012)


    SELECT CASE WHEN MONTH(DueDate)=5 THEN DueDate END AS DT

    FROM [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.

  • 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

  • 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