null values

  • I have a query that may return null values in one particular column. If there are null values in that column for every row in the dataset, i dont want to return any data (if there is at least one row with a non null value, then return data)

    How could this be done ?

  • Are you saying that if, for instance, column c is null for a particular row, don't return that row.

    select

    *

    from

    dbo.MyTable

    where

    c is not null;

  • Lynn's method filters out the rows with NULLs, and that's how you should check for null. Don't use = NULL or !NULL, use IS NULL or IS NOT NULL.

    If you want to still show those rows, but not the NULL, look up ISNULL() the function.

  • no, what I actually want to do is return the entire dataset if at least one row has a value in the nullable column, if there are all nulls, dont return anything. I cant use

    where column_name IS NOT NULL because that will exclude null values completely.

    for example, the query may return 10 rows, one of those rows in column 'x' has a value, the other 9 are null, so I want all 10 rows.

    If the query returned 10 rows, and all values in column 'x' are null, dont return anything

  • Then use a modified version of what Lynn put together, but in an EXISTS clause

    select

    *

    from

    dbo.MyTable

    where

    Exists (

    select Null

    from dbo.MyTable

    where c is not null

    );

    The smiley face is a closed parenthesis by the way

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May I suggest that you read the article I have referenced below in my signature block regarding asking for help. Based on your posts, I really don't know what you are trying to accomplish. You have to excuse me, but I am more visual in my problem soving. Show me what you are trying to accomplish.

  • I ended up creating a function that returned a count and checked if the count was greater than zero. Probably not the most elegant or efficient way but it does work. Anyone got any suggestions for a more elegant way ?

    SELECT d.[ts_date], d.[ts_series_id], ROUND(d.[ts_value], 2) AS [ts_value], m.[ts_unit]

    FROM ts_data d

    INNER JOIN ts_matrix m on d.ts_series_id = m.ts_series_id

    WHERE m.ts_link_id = 1872

    AND d.ts_date BETWEEN 'Dec 16 2007 12:00:00:000AM' AND 'Dec 15 2008 12:00:00:000AM'

    AND m.ts_frequency_id = (SELECT MIN(m2.ts_frequency_id)

    FROM ts_data d2

    INNER JOIN ts_matrix m2 on d2.ts_series_id = m2.ts_series_id

    WHERE m2.ts_link_id = 1872

    AND d2.ts_date BETWEEN 'Dec 16 2007 12:00:00:000AM' AND 'Dec 15 2008 12:00:00:000AM')

    AND dbo.CountNulls(1872,'Dec 16 2007 12:00:00:000AM','Dec 15 2008 12:00:00:000AM') > 0

    ORDER BY d.[ts_date] asc

    heres the function

    ALTER FUNCTION [dbo].[CountNulls]

    (

    -- Add the parameters for the function here

    @id int,

    @StartDate DateTime,

    @EndDate DateTime

    )

    RETURNS int

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @RetVal int

    SELECT @RetVal = COUNT(d.[ts_data_id])

    FROM ts_data d

    INNER JOIN ts_matrix m on d.ts_series_id = m.ts_series_id

    WHERE m.ts_link_id = @id

    AND d.ts_date BETWEEN @StartDate AND @EndDate

    AND d.ts_value IS NOT NULL

    AND m.ts_frequency_id = (SELECT MIN(m2.ts_frequency_id)

    FROM ts_data d2

    INNER JOIN ts_matrix m2 on d2.ts_series_id = m2.ts_series_id

    WHERE m2.ts_link_id = @id

    AND d2.ts_date BETWEEN @StartDate AND @EndDate)

    -- Return the result of the function

    RETURN @RetVal

    END

  • Do you want a better answer? If yes, then read the following article http://www.sqlservercentral.com/articles/Best+Practices/61537/

    and following the guidelines within it to provide us with everything we need to help you better.

    I am sure that there is a better way than using the function you wrote, but without the DDL for the table, sample data, and expected results, its hard to know.

  • I'd strongly suggest you use Matt's solution. The one you have is going to perform pretty badly.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply