Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ISNULL fUNCTION

Posted on 2011-03-21
25
Medium Priority
?
472 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 61

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 61

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 61

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 61

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

579 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