Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ISNULL fUNCTION

Posted on 2011-03-21
25
Medium Priority
?
457 Views
Last Modified: 2012-05-11
resultSet.xls


Hi all,
I have simple requirement, wanting to replace the Null Values with '-' in my code, please find the attachment. I know this could be achieved by ISNULL function, not sure  where exactly to implement in the  code.
Also want to  create a new table from this result set. Something like Select * into [tablename] from [oldtablename]

Hope am clear.

Any suggestions are highly appreciated. resultSet.xls
declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols + 
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate,Disposition from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)

Open in new window

0
Comment
Question by:parpaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
  • 4
  • +1
25 Comments
 

Author Comment

by:parpaa
ID: 35186929
'REPLACE FN ON DISPOSITION COLUMN'

THANKS
0
 

Author Comment

by:parpaa
ID: 35186936
SORRY i MEANT ISNULL FUNCTION
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35186946
You can use case also like this

 declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then '-' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35186950
Using ISNULL

 declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, ISNULL(Disposition , '-') AS Disposition   from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
0
 

Author Comment

by:parpaa
ID: 35186955
Msg 8117, Level 16, State 1, Line 9
Operand data type nvarchar is invalid for subtract operator.
0
 

Author Comment

by:parpaa
ID: 35186964
Pratima,

It is not necessary that I have to go for '-' sign, any other character will do.
Hope am clear
0
 

Author Comment

by:parpaa
ID: 35186983
@ Pratima

I tried with ISNULL Function, throwing me error.


Msg 8117, Level 16, State 1, Line 8
Operand data type nvarchar is invalid for subtract operator.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35187030
try this

declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, ISNULL(Disposition ,
"-") AS Disposition   from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
0
 

Author Comment

by:parpaa
ID: 35188313
No Luck :(
Msg 207, Level 16, State 1, Line 2
Invalid column name '-'.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35188368
what about:
declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)

Open in new window

0
 

Author Comment

by:parpaa
ID: 35188392
@Thanks Angel for your suggestion.

Unfortunately there is no change I still see the null values.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35188417
try this

declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, ISNULL(Disposition ,
'''-''') AS Disposition   from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
0
 

Author Comment

by:parpaa
ID: 35188438
Msg 8117, Level 16, State 1, Line 9
Operand data type nvarchar is invalid for subtract operator.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35188477
what about:
declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End) for Calldate in (' + @cols + ')) as p'
exec (@query)

Open in new window

0
 

Author Comment

by:parpaa
ID: 35188522

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'convert'.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35188546
well, actually: is the column value NULL or empty string?
please
0
 

Author Comment

by:parpaa
ID: 35190320
@Angel,

Please find the attachment of my Source table content.
Hope you get an idea.
Thnk you
Source-Table.xlsx
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35190563
The issue is you are pivoting, IMHO, so the NULLs are when a particular row has a column after pivot for which it didn't actually have a row/record of data; therefore, wrapping the original query with ISNULL does nothing as you are finding.

To fix, you need to generate a second listing of volume that has definitions like:
'ISNULL('+col1+',''-'') as '+col1

Not on a full keyboard, but hopefully you get the drift.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35190597
Volume = columns
I.e., if the second variable/listing of column names is @colsSel then you would just replace @cols in the select portion of the query with @colsSel. The instance of @cols in the pivot clause would stay the same.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35190764
here is my testing code.
I see that the isnull() has to go like this:
create table  [Source table] ( [Mobile No] varchar(100),	calldate datetime, 	Disposition	varchar(10), HUB varchar(10) )
go
set dateformat dmy
go

insert into [Source table] values('423432657','12/09/2010 00:00','PTP','KK')
insert into [Source table] values('12345823','13/09/2010 00:00','PTP','KK')
insert into [Source table] values('2395586','14/09/2010 00:00','PTP','KK')
insert into [Source table] values('23902334','15/09/2010 00:00','PTP','KK')
insert into [Source table] values('34853433','16/09/2010 00:00','PTP','KK')
insert into [Source table] values('2380334','17/09/2010 00:00','PTP','KK')
insert into [Source table] values('32323854','18/09/2010 00:00','PTP','KK')
insert into [Source table] values('23823844','19/09/2010 00:00','PTP','KK')
insert into [Source table] values('2380232','20/09/2010 00:00','NB','KK')
insert into [Source table] values('83235671','21/09/2010 00:00','NB','KK')
insert into [Source table] values('84235671','22/09/2010 00:00','NB','AP')
go

declare @cols1 nvarchar(max)
declare @cols2 nvarchar(max)
 select  @cols1 = stuff(( select distinct top 100 percent ',isnull([' + convert(varchar,t2.Calldate,105) + '],''-'')'
                           from    [Source table] as t2
                          order by 1
                            for xml path('')), 1, 1, '') + ''

 select  @cols2 = stuff(( select distinct top 100 percent ',[' + convert(varchar,t2.Calldate,105) + ']'
                           from    [Source table] as t2
                          order by 1
                            for xml path('')), 1, 1, '') + ''
declare @query nvarchar(max)
set @query = 'select [Mobile no],' + @cols1 + ' 
from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols2 + ')) as p'
print @query
exec (@query)
go
go
select * from [Source table]
go
drop table [source table]

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35190824
Exactly, a3...note, you will want to repeat the column value piece or you will end up with an unnamed column in the final selection since the is null test being an expression needs to be aliased.
0
 

Author Comment

by:parpaa
ID: 35190908
That worked perfectly fine. Thanks @Angel

BTW The column names have no name: the column name is supposed to be dates.
Please refer to my resultset file attached.
resultSet.xls
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35191029
updated T-SQL
declare @cols1 nvarchar(max)
declare @cols2 nvarchar(max)
 select  @cols1 = stuff(( select distinct top 100 percent ',isnull([' + convert(varchar,t2.Calldate,105) + '],''-'') [' + convert(varchar,t2.Calldate,105) + ']'
                           from    [Source table] as t2
                          order by 1
                            for xml path('')), 1, 1, '') + ''

 select  @cols2 = stuff(( select distinct top 100 percent ',[' + convert(varchar,t2.Calldate,105) + ']'
                           from    [Source table] as t2
                          order by 1
                            for xml path('')), 1, 1, '') + ''
declare @query nvarchar(max)
set @query = 'select [Mobile no],' + @cols1 + ' 
from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols2 + ')) as p'
print @query
exec (@query)

Open in new window

0
 

Author Closing Comment

by:parpaa
ID: 35191118
Thanks again :)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35192006
Glad we were able to help you!
Best regards and happy coding,
Kevin
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question