Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ISNULL fUNCTION

Posted on 2011-03-21
25
Medium Priority
?
463 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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview

963 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