rename NULL column outputs

I have this view:
CREATE VIEW CautionDescriptions AS
SELECT TOP 100 PERCENT
Oils.OilName AS [Oil name], Cautions.Description AS Caution
FROM Cautions
INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
ORDER BY Oils.OilName
I want to return all rows even those with NULLs in the description key. Is
there a way to replace the <NULL>s in the description column with more
attractive text?
like
if OilCautions.OilID ISNULL 'No description available'
endif
Where could I put this in the SELECT or FROM clause?SELECT COALESCE(column_name, 'No description available')
You can also use ISNULL. Funny, I just wrote an article about this earlier
today:
http://www.aspfaq.com/2532
http://www.aspfaq.com/
(Reverse address to reply.)
"Patrick" <psully@.nospam.eatel.net> wrote in message
news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
> I have this view:
> CREATE VIEW CautionDescriptions AS
> SELECT TOP 100 PERCENT
> Oils.OilName AS [Oil name], Cautions.Description AS Caution
> FROM Cautions
> INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
> RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
> ORDER BY Oils.OilName
> I want to return all rows even those with NULLs in the description key. Is
> there a way to replace the <NULL>s in the description column with more
> attractive text?
> like
> if OilCautions.OilID ISNULL 'No description available'
> endif
> Where could I put this in the SELECT or FROM clause?
>
>|||Thanks Aaron! That was a fast answer. I never heard of coalesce. I am going
to read your article too, thanks again!
BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
Programming. The book name is certainly a misnomer, the book is mostly about
using Enterprise Manager.
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||Read your article. **** rating!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||> BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
> Programming. The book name is certainly a misnomer, the book is mostly
about
> using Enterprise Manager.
Then you might want to read this article too:
http://www.aspfaq.com/2455
:-)
PS I suggest creating your views from scratch, as opposed to letting
Enterprise Manager write the code for you ... because of the limitations in
the view designer, as well as its funky quirks like putting TOP 100 PERCENT
and ORDER BY in views for some reason.|||Oops, I think I hit reply too soon (may be an empty reply here soon).
Anyway, that was an interesting and informative article. I've taken a few
SQL Server courses, and they all start of with EM. But Step by Step does
cover the QA, too. I remember vaguely when I learned how to use it a little
last year, I was impressed a lot by QA. I mainy use EM for experimenting
with joins really fast, and if it works, copy the script and edit it in QA.
When are you going to turn all these "tips" into a book? lol, thanks!
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ur6ou3YAFHA.4072@.TK2MSFTNGP10.phx.gbl...
2000
> about
> Then you might want to read this article too:
> http://www.aspfaq.com/2455
> :-)
> PS I suggest creating your views from scratch, as opposed to letting
> Enterprise Manager write the code for you ... because of the limitations
in
> the view designer, as well as its funky quirks like putting TOP 100
PERCENT
> and ORDER BY in views for some reason.
>

Removing Nulls

Hello Everyone, I have a View that is returning null values. This is not a problem except that I don't want the user to see "NULL" in the data. I want to replace "NULL" with a blank. The sql that I have is shown below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.CUSVW_SCHEMATIC_DETAILS_VENDOR
AS
SELECT [DIVISION], [DIVISION NAME], [STOCKING SECTION #], [STOCKING SECTION NAME],
[FIXTURE], [SIZE], [STORE #], [AISLE NUMBER], [AISLE SIDE],
[LEFT STOCKING SECTION #], [LEFT STOCKING SECTION NAME],
[LEFT STOCKING SECTION SIZE], [RIGHT STOCKING SECTION #],
[RIGHT STOCKING SECTION NAME], [RIGHT STOCKING SECTION SIZE]
FROM [AVTP].[dbo].[CUSVW_SCHEMATIC_DETAILS]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

How can I get the nulls to be blanks instead of "NULL"??

Thanks!

Quote:

Originally Posted by jschmidt

Hello Everyone, I have a View that is returning null values. This is not a problem except that I don't want the user to see "NULL" in the data. I want to replace "NULL" with a blank. The sql that I have is shown below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.CUSVW_SCHEMATIC_DETAILS_VENDOR
AS
SELECT [DIVISION], [DIVISION NAME], [STOCKING SECTION #], [STOCKING SECTION NAME],
[FIXTURE], [SIZE], [STORE #], [AISLE NUMBER], [AISLE SIDE],
[LEFT STOCKING SECTION #], [LEFT STOCKING SECTION NAME],
[LEFT STOCKING SECTION SIZE], [RIGHT STOCKING SECTION #],
[RIGHT STOCKING SECTION NAME], [RIGHT STOCKING SECTION SIZE]
FROM [AVTP].[dbo].[CUSVW_SCHEMATIC_DETAILS]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

How can I get the nulls to be blanks instead of "NULL"??

Thanks!

try:

SELECT isnull(DIVISION,' ') as division...and so on...|||Try using case statements

case when division is null then '' esle division end

sort of thins, this need to be in the select statement

Removing null values from a result set

Hi,

I have following query which is returing null values along with other values. Could you please tell me how I can restrict to not to return null values

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Thanks,

Prudhvi

Prud,

I'm assuming that you want to filter out TotalHours

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Where [Measures].[Total Hours] is not null

Ham

|||

Thanks Ham. I did tried this earlier but got the following error message

Executing the query ...
Query (5, 1) The IS function expects a member expression for the 2 argument. A string or numeric expression was used.
Execution complete

|||

Prud,

Are you using SQL 2000 or SQL 2005? The "is not null" is a valid for SQL in the Where clause.

Ham

|||

SSAS 2005 introduces a HAVING clause:

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]

HAVING [Measures].[Total Hours] <> 0 ON 1
FROM[Labor Metrics]

Or, you can use Filter().

|||

Thanks Teo, it worked.

Removing NULL in CASE ELSE

How to compress the output into single line
if got the below output using case..else
9155 michael NULL NULL NULL
9155 NULL NULL narain NULL
9155 NULL NULL NULL karthik
9155 NULL shumaker NULL NULL
Thanks
krishHi Krish,
Could you please be so kind give us more specific details that are you tryin
g?
Thanks in advance and regards,
"krish" wrote:

> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish|||Try grouping by the first column and using min or max aggregate function.
Example:
select col1, min(case when ... end), min(case when ... end)
from table1
group by col1
AMB
"krish" wrote:

> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish|||SELECT a, MAX(b), MAX(c), MAX(d), MAX(e)
FROM
(
SELECT 9155 as a, 'michael' as b, NULL AS c, NULL AS d, NULL AS e
UNION
SELECT 9155, NULL, NULL, 'narain', NULL
UNION
SELECT 9155, NULL, NULL, NULL, 'karthik'
UNION
SELECT 9155, NULL, 'shumaker', NULL, NULL
) AS sub
GROUP BY a
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"krish" <krish@.discussions.microsoft.com> wrote in message
news:A859613F-79B8-4BF2-840E-CA33BB9F42B2@.microsoft.com...
> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish