using isnull - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using isnull

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2008-11-08 : 09:11:45

Hello,
I know if I want to replace a null column i can use isnull.

But what is the result set is null and I want to replace the values with 3 records ie

If the results set is null, then the result set should be replaced with 3 columns as thus

general categories
love
birthdays

How can i achieve this ?

thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-08 : 12:22:07

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114023

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 12:30:41

seems like what you want is if

select fields... into #temp from yourtable....

if (select count(*) from #temp)=0
 select null as [general categories],
nul as love,
null as birthdays
else
select * from #temp

if this is not what you're looking for,please illustrate your scenario with some sample data.

missMac
Posting Yak Master

124 Posts

Posted - 2008-11-09 : 02:50:04

quote:
Originally posted by visakh16

seems like what you want is if

select fields... into #temp from yourtable....

if (select count(*) from #temp)=0
select null as [general categories],
nul as love,
null as birthdays
else
select * from #temp

if this is not what you're looking for,please illustrate your scenario with some sample data.

Yes sir,
this is what I want, only problem is that the results set are in rows instead of columns, is there anyway to format it as a column ?

Here is my code


if (Select  top (1) categoryname+' ('+CAST(count(categoryname) as varchar(20))+')'   as total from scheduledMessages_outbox where username = 'sally'group by categoryname) is not null
select isnull(null,'general categories') as [general categories],
isnull(null,'love') as love,
isnull(null,'birthdays') as birthdays
else
Select  categoryname+' ('+CAST(count(categoryname) as varchar(20))+')'   as total from scheduledMessages_outbox where username = 'sally'group by categoryname

Result of the above is thus

general categories love birthdays
general categories love birthdays

(1 row(s) affected)

I want results as thus

Result Set

general categories
love
birthdays
(3 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 04:24:16

[code]if (Select top (1) categoryname+' ('+CAST(count(categoryname) as varchar(20))+')' as total from scheduledMessages_outbox where username = 'sally'group by categoryname) is not null
begin
select 'general categories' as [column value]
union all
select 'love'
union all
select 'birthdays'
end
else
Select categoryname+' ('+CAST(count(categoryname) as varchar(20))+')' as total from scheduledMessages_outbox where username = 'sally'group by categoryname[/code]

missMac
Posting Yak Master

124 Posts

Posted - 2008-11-09 : 06:52:55

Ok thank you.

One more question.

If i run a select command to get a couple of rows that exist, Is it possible to.

1. Check if certain rows are in the selected list ie check if "GENERAL CATEGORIES, LOVE, BIRTHDAY"
2. If they are not contained, append them.

the difference between this and the above is that the above appends if null. However if it exists, how do i check for them and append them.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 11:25:20

1.you can check if they exists using
IF EXISTS()
2.In else condition you can add code to append them. A simple insert query will do.

missMac
Posting Yak Master

124 Posts

Posted - 2008-11-09 : 15:50:09

Ok thanks
do i run the if exists for each one ? thats running it 3 times ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 23:28:56

ok..in that case just make a derived table with required three values ("GENERAL CATEGORIES, LOVE, BIRTHDAY") and then take LEFT JOIN yourtable with this and insert.

something like


INSERT INTO DestTable
SELECT fields
FROM (select 'general categories' as [column value]
union all
select 'love'
union all
select 'birthdays'
)tmp
LEFT JOIN  SourceTable t
ON tmp.[column value]=t.columnname
WHERE t.columnname IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources