from NULL to 0 - 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

from NULL to 0

Author

Topic

SebJ
Starting Member

11 Posts

Posted - 2011-10-19 : 02:43:52

You might think this is very simple but I do have a serious problem with the following:

I used dynamic SQL in order to to get a pivot table which has a changing number of columns. Depending on several parameters, the table could look like this (static number of parameters and N p_columns):

parameter1 | parameter2 | parameter3 | p_column1 | p_column2 | p_column3 | ... | p_columnN

All this is functioning quiet well... Now what I just want to do is to adjust the values contained in these green p_columns there! But of course ISNULL() just works for a simple column.
Is there a way or even a not known function in order to just check the whole table and replace each NULL by 0? This would already suit my requirements. I´d like to avoid some extensive dynamic commands and so on...

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 04:14:56

why cant you apply ISNULL() over each column and change it to 0?
even in dynamic statement where you generate columnlist, you can add isnull( ,0) to each of items. If you can show how you generate column list, we can help you to add this in list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-19 : 04:18:59

quote:
Is there a way or even a not known function in order to just check the whole table and replace each NULL by 0?

That will not help. As you are pivoting the result. You will still get NULL value if a certain combination does not exists

KH
[spoiler]Time is always against us[/spoiler]

SebJ
Starting Member

11 Posts

Posted - 2011-10-19 : 06:50:00

Hi, the best would be to show you some stuff I tried previously. This is my code right now:

... [some previous stuff, which works after all] ...

SELECT IDENTITY(int) AS ID, *
INTO #TBL
FROM initialTBL

DECLARE @pivot varchar(max), @pivot2 varchar(max), @sql varchar(max), @select varchar(2000), @PivotCol varchar(100), @Summaries varchar(100)

SET @select = 'select ID, TimeInterval, WeekID, ValidID, Value from #helpTBL'

SET @PivotCol = 'SegmentID'
SET @Summaries = 'avg(Value)'
SET @select = REPLACE(@select,'select ','select ' + @PivotCol + ' as pivot_col,')

CREATE TABLE #pivot_columns (pivot_column varchar(100))
SET @sql = 'select code from #TBL as t'
INSERT INTO #pivot_columns
EXEC (@sql)

SELECT @pivot = COALESCE(@pivot + ',','') + '[' + pivot_column + ']' FROM #pivot_columns

SELECT @pivot2 = COALESCE(@pivot + ', ','') + 'ISNULL([' + pivot_column + '], 0) AS ' + pivot_column FROM #pivot_columns

SET @sql = 'select ID, TimeInterval, WeekID, ValidID from (' + @select + ') as t pivot (' + @Summaries + ' for pivot_col in (' + @pivot + ')) as p

EXEC (@sql)

Lastly, I tried to save the new pivot table into a temptable but I failed (somehow)...

Grateful for any help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 07:14:30

what was error when you tried this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SebJ
Starting Member

11 Posts

Posted - 2011-10-19 : 07:55:18

The Error: " Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '12602' "

12602 means the name of the very last column, so the last 'pivoted' column... I already checked commata and stuff - I found nothing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 08:18:50

put PRINT(@Sql) instead of EXEC(@SQL) and post the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SebJ
Starting Member

11 Posts

Posted - 2011-10-20 : 05:04:55

Guys, thanks again! I found the causation:

(code see above...)

SELECT @pivot2 = COALESCE(@pivot + ', ','') + 'ISNULL([' + pivot_column + '], 0) AS ' + pivot_column FROM #pivot_columns

it must be:

SELECT @pivot2 = COALESCE(@pivot2 + ', ','') + 'ISNULL([' + pivot_column + '], 0) AS ' + pivot_column FROM #pivot_columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 05:35:49

ok cool...best way to debug dynamic sql is to use PRINT to display sql and see the string generated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources