October 23, 2009 at 6:52 am
Hi,
Have a table with null values. I am doing a SELECT query as follows:
SELECT Project_ID + ' ' + Task + ' ' + Sub_Task + ' ' Proj_Desc As Project
FROM dbo.TMS_Proj
ORDER BY Project_ID
Both of the columns Task and Sub_Task contain null values. Therefore, the query omits the rows with null values and displays only those with non-null entries in those two categories. I want to display ALL of the rows from this table. I checked books and forums and am confused as to how to successfully approach this. Help is gratefully appreciated.
JohnShell
October 23, 2009 at 7:36 am
Based on the query you've supplied, I see no reason for the query to exclude rows that contain NULL values. This would only be the case is you were perhaps joining or grouping or your where statement had something like WHERE Mycol IS NOT NULL or other such thing...
Can you please provide some sample data to include table definitions and such as described in the first link in my signature? By supplying that information you will help us be ablle to better review your code and see what's going on.
-Luke.
October 23, 2009 at 7:47 am
You will be getting NULL if any one of the columns you are concatenating is NULL.
If this is what you are trying to avoid, put SET CONCAT_NULL_YIELDS_NULL OFF before you execute the select statement, or change the select to this:-
SELECT COALESCE(Project_ID,'') + ' ' + COALESCE(Task,'') + ' ' + COALESCE(Sub_Task,'') + ' ' Proj_Desc As Project
FROM dbo.TMS_Proj
ORDER BY Project_ID
October 23, 2009 at 7:48 am
As Luke has said there is nothing in the query that would omit rows being returned when there are NULL values in any of the columns. Now if you mean that the rows where 1 of the columns has a NULL value return NULL then that is the designed behavior of SQL Server when you concatenate NULLS. To do concatenation when there are columns that could be NULL should wrap the columns that allow NULLS in an ISNULL or COALESCE like this:
SELECT
Project_ID + ' ' + Task + ' ' + ISNULL(Sub_Task, '') + ' ' + ISNULL(Proj_Desc, '') As Project
FROM
dbo.TMS_Proj
ORDER BY
Project_ID
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 23, 2009 at 8:40 am
Perfect! Thank you very much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply