NUll values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NUll values

Author

Topic

Julie19
Starting Member

32 Posts

Posted - 2012-07-26 : 15:33:00

I have table where some of the columns have Null values.But When I create a view these null values are dispayed as"Zeroes".Is it possible to show null values in SQL view

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-26 : 15:42:55

This should not happen. It might be something in the view that is doing it - look for things like ISNULL(Col1,0) or COALESCE(Col1,0). Post the code for the view if you like. See this example below, it shows NULLs as NULLs.

-- table
CREATE TABLE ATable (id INT);
INSERT INTO Atable VALUES (1),(NULL),(NULL);

-- view
CREATE view Aview  AS  SELECT id FROM Atable;

-- select
SELECT * FROM Aview

-- Result shows nulls
--id
--1
--NULL
--NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-26 : 15:43:10

How are you displaying the view? They show as null for me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Julie19
Starting Member

32 Posts

Posted - 2012-07-27 : 12:47:47

Here is the code

set @SQL ='CREATE VIEW [24hr_AM_VIEW] AS Select m.Function, m.Ct, m.AMID,m.AM
,ISNULL(SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt21,120) + '''' + ' THEN m.[1DScr] ELSE 0 END),NULL) AS ['+ convert(varchar(25), @dt23, 120) + ']'
+ ',ISNULL(SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt21, 120) + '''' + ' THEN m.[1DAttain] ELSE 0 END),NULL) AS ['+ convert(varchar(25), @dt23, 120) +'1DAttain]'

And also I am getting the warning: null value is eliminated by an aggregate or other set operation.

I am using SQL server 2008

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 13:26:01

Try changing it to this - all I am doing is removing the ISNULL function and the else clause in the case expressions.

SET @SQL = 
    'CREATE VIEW [24hr_AM_VIEW] AS Select m.Function, m.Ct, m.AMID,m.AM
,SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + CONVERT(VARCHAR(25), @dt21, 120) 
    + '''' + ' THEN m.[1DScr] END) AS [' + CONVERT(VARCHAR(25), @dt23, 120) 
    + ']'
    + ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + 
    CONVERT(VARCHAR(25), @dt21, 120) + '''' + 
    ' THEN m.[1DAttain] END)) AS [' + CONVERT(VARCHAR(25), @dt23, 120) 
    + '1DAttain]'

Couple of comments though:

1. It looks like you are trying to create a view for a 24 hour period (or some specific period of time). If that indeed is the case, there are other easier and safer ways to do it. If you can describe what you are trying to do, many people on the forum would be able to offer suggestions.

2. When comparing dates, it is preferable to compare them as dates rather than converting to character strings and comparing for both efficiency and accuracy reasons.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-27 : 13:31:20

Removing the ISNULL function shouldn't make a difference. But, want we really need the DDL for the view, not the code to generate the view. Plus some sample data might help or how to generate the code for the view or something, you know the basics..

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Julie19
Starting Member

32 Posts

Posted - 2012-07-27 : 14:09:32

I see the null values when I run the same view in 2012 without having to remove the ISNULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-27 : 16:52:58

You should see them in any version of SQL the ISNULL function hasn't changed in a long time (if ever).

Julie19
Starting Member

32 Posts

Posted - 2012-07-30 : 11:44:46

This is my view

'CREATE VIEW [24hr_VIEW] AS Select m.Function, m.Ctr, m.ID,m.AM
,ISNULL(SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt21,120) + '''' + ' THEN m.[1Scr] ELSE 0 END),NULL) AS ['+ convert(varchar(25), @dt23, 120) + ']'
+ ',ISNULL(SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt21, 120) + '''' + ' THEN m.[1DayDailyAttain] ELSE 0 END),NULL) AS ['+ convert(varchar(25), @dt23, 120) +'1DlyAtt]'

What am I doing wrong here?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-30 : 11:55:05

If you are trying to get the two aggregate columns to be null, then make the changes shown in red below:

'CREATE VIEW [24hr_VIEW] AS Select m.Function, m.Ctr, m.ID,m.AM
,NULLIF(SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt21,120) + '''' + ' THEN m.[1Scr] ELSE 0 END),0) AS ['+ convert(varchar(25), @dt23, 120) + ']'
+ ',NULLIF(SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt21, 120) + '''' + ' THEN m.[1DayDailyAttain] ELSE 0 END),0) AS ['+ convert(varchar(25), @dt23, 120) +'1DlyAtt]'

Julie19
Starting Member

32 Posts

Posted - 2012-07-30 : 12:09:29

This query is displaying null values although the table has zeros in the columns

I have tried this

',ISNULL(SUM(CASE WHEN convert(varchar(10), ReportDate) = '+ ''''+ convert(varchar(25), @dt, 120) + '''' + ' THEN a.dly END),NULL) AS ['+ convert(varchar(25), @dt2, 120) + ']'
+ ',ISNULL(SUM(CASE WHEN convert(varchar(10), ReportDate) = '+ ''''+convert(varchar(25), @dt, 120) + '''' + ' THEN a.[1DlyAtt] END ),NULL) AS ['+ convert(varchar(25), @dt2, 120) + '1DlyAttain]'

and this one displays nulls if teh table data has nulls and zeroes it the table data has zeros in the columns

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-30 : 12:20:54

I am totaly confused

quote:
Originally posted by Julie19

I have table where some of the columns have Null values.But When I create a view these null values are dispayed as"Zeroes".Is it possible to show null values in SQL view

quote:
Originally posted by Julie19

This query is displaying null values although the table has zeros in the columns

quote:
Originally posted by Julie19

I see the null values when I run the same view in 2012 without having to remove the ISNULL

Is it displaying nulls? Is it displaying zero?

We don't have access to your data, so please, help us help you. Please provide some sample data and describe what you want for output (expected results). Becuase what the view currently does doesn't matter; We need to know what you want it to do.

Julie19
Starting Member

32 Posts

Posted - 2012-07-30 : 12:23:20

I appologize for all the confusion

I think it is working now

Thank you everyone for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources