Solved

ISNULL fUNCTION

Posted on 2011-03-21
25
453 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 59

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 59

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 59

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 500 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 59

Expert Comment

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

Featured Post

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.

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 …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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