In the following code
SELECT x.[First Name] + ' ' + x.[Last Name] AS 'Combined Name' FROM [Database1].[dbo].[InfoTable] x WHERE 'Combined Name' IS NOT NULL
Combined table still pops up with Nulls.
Questions: Why? Is it because of the WHERE statement? Is it only acceptable to use fully qualified names such as
SELECT x.[First Name] + ' ' + x.[Last Name] AS 'Combined Name' FROM [Database1].[dbo].[InfoTable] x WHERE x.[First Name] + ' ' + x.[Last Name] IS NOT NULL
Things I’ve tried..different naming conventions and syntax, etc. The fully qualified names version works as expected, hence the question..just curious if I can use a shorter syntax that isn’t completely spelled out. (In this example code was borrowed from a statement with multiple joins, so assume there are other tables in play)
Advertisement
Answer
This condition:
WHERE 'Combined Name' IS NOT NULL
Is comparing the constant string 'Combined Name'
to NULL
. It is evidently not NULL
so this never returns false.
I would recommend that you never use single quotes to refer to column names — too easily confused with strings as you do here. The best bet is to choose names, such as combined_name
that do not need to be escaped.
Oh, the answer to what you want to do is that you need a CTE, subquery, or lateral join, because you cannot refer to the column name in a where
. I might suggest:
SELECT v.combined_name FROM [Database1].[dbo].[InfoTable] x CROSS APPLY (VALUES (x.[First Name] + ' ' + x.[Last Name]) ) v(combined_name) WHERE v.combined_name IS NOT NULL;