December 15, 2008 at 9:09 am
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 ?
December 15, 2008 at 9:26 am
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;
December 15, 2008 at 9:34 am
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.
December 15, 2008 at 9:44 am
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
December 15, 2008 at 9:48 am
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?
December 15, 2008 at 9:50 am
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.
December 15, 2008 at 9:59 am
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
December 15, 2008 at 10:05 am
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.
December 15, 2008 at 10:18 am
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