parpaa
asked on
ISNULL fUNCTION
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
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)
ASKER
SORRY i MEANT ISNULL FUNCTION
You can use case also like this
declare @cols nvarchar(2000)
select @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldat e,105)
from [Source table] as t2
order by '],[' + convert(varchar,t2.Calldat e,105)
for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
'from (select [Mobile no],convert(varchar,CallDa te,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)
declare @cols nvarchar(2000)
select @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldat
from [Source table] as t2
order by '],[' + convert(varchar,t2.Calldat
for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
'from (select [Mobile no],convert(varchar,CallDa
pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
Using ISNULL
declare @cols nvarchar(2000)
select @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldat e,105)
from [Source table] as t2
order by '],[' + convert(varchar,t2.Calldat e,105)
for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
'from (select [Mobile no],convert(varchar,CallDa te,105) CallDate, ISNULL(Disposition , '-') AS Disposition from [Source table]) t1
pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
declare @cols nvarchar(2000)
select @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldat
from [Source table] as t2
order by '],[' + convert(varchar,t2.Calldat
for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
'from (select [Mobile no],convert(varchar,CallDa
pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
ASKER
Msg 8117, Level 16, State 1, Line 9
Operand data type nvarchar is invalid for subtract operator.
ASKER
Pratima,
It is not necessary that I have to go for '-' sign, any other character will do.
Hope am clear
It is not necessary that I have to go for '-' sign, any other character will do.
Hope am clear
ASKER
@ 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.
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.
try this
declare @cols nvarchar(2000)
select @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldat e,105)
from [Source table] as t2
order by '],[' + convert(varchar,t2.Calldat e,105)
for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
'from (select [Mobile no],convert(varchar,CallDa te,105) CallDate, ISNULL(Disposition ,
"-") AS Disposition from [Source table]) t1
pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
declare @cols nvarchar(2000)
select @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldat
from [Source table] as t2
order by '],[' + convert(varchar,t2.Calldat
for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
'from (select [Mobile no],convert(varchar,CallDa
"-") AS Disposition from [Source table]) t1
pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
ASKER
No Luck :(
Msg 207, Level 16, State 1, Line 2
Invalid column name '-'.
Msg 207, Level 16, State 1, Line 2
Invalid column name '-'.
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)
ASKER
@Thanks Angel for your suggestion.
Unfortunately there is no change I still see the null values.
Unfortunately there is no change I still see the null values.
try this
declare @cols nvarchar(2000)
select @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldat e,105)
from [Source table] as t2
order by '],[' + convert(varchar,t2.Calldat e,105)
for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
'from (select [Mobile no],convert(varchar,CallDa te,105) CallDate, ISNULL(Disposition ,
'''-''') AS Disposition from [Source table]) t1
pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
declare @cols nvarchar(2000)
select @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldat
from [Source table] as t2
order by '],[' + convert(varchar,t2.Calldat
for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
'from (select [Mobile no],convert(varchar,CallDa
'''-''') AS Disposition from [Source table]) t1
pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
ASKER
Msg 8117, Level 16, State 1, Line 9
Operand data type nvarchar is invalid for subtract operator.
Operand data type nvarchar is invalid for subtract operator.
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)
ASKER
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'convert'.
well, actually: is the column value NULL or empty string?
please
please
ASKER
@Angel,
Please find the attachment of my Source table content.
Hope you get an idea.
Thnk you
Source-Table.xlsx
Please find the attachment of my Source table content.
Hope you get an idea.
Thnk you
Source-Table.xlsx
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.
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.
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.
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.
here is my testing code.
I see that the isnull() has to go like this:
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]
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.
ASKER
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
BTW The column names have no name: the column name is supposed to be dates.
Please refer to my resultset file attached.
resultSet.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again :)
Glad we were able to help you!
Best regards and happy coding,
Kevin
Best regards and happy coding,
Kevin
ASKER
THANKS