Case - 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)

Case

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-17 : 21:55:12

"case" is not working with dif columns. I want to check col1 first , then col2 ,...
Eg.
case when col1=NULL then 'AA'
when col2=NULL then 'BB'
else 'CC' end As test

It always return 'CC'

Tks.

Sachin.Nand

2937 Posts

Posted - 2010-02-17 : 22:15:59

Try when col1 IS NULL & col2 IS NULL

PBUH

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-17 : 22:26:54

I want to check col1 first , if it meets condition return something and exit case. Otherwise, check col2,...

quote:
Originally posted by Idera

Try when col1 IS NULL & col2 IS NULL

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-02-17 : 22:29:43

case when col1 IS NULL then 'AA'
when col2 IS NULL then 'BB'
else 'CC' end As test

PBUH

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-17 : 22:54:03

Thanks.
Below is my some portion of query. It gives error
"Conversion failed when converting the varchar value 'N/A' to data type int." when it is executed. I thnk error came from else portion coz it works if I change else portion to 'test'.

case when col1 IS NOT NULL then 'N/A'
when GDate IS NULL then '0'
else (SELECT CASE WHEN RDate <> NULL THEN DATEDIFF(dd,GDate,RDate)- COUNT(*)
ELSE DATEDIFF(dd,GDate,getdate())- COUNT(*)
END AS WeekDays
FROM WeekEndsAndHolidays2010
WHERE DayOfWeekDate BETWEEN GDate AND Getdate())
end as NoDays

quote:
Originally posted by Idera

case when col1 IS NULL then 'AA'
when col2 IS NULL then 'BB'
else 'CC' end As test

PBUH

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-17 : 23:09:34

Solved .

quote:
Originally posted by kwikwisi

Thanks.
Below is my some portion of query. It gives error
"Conversion failed when converting the varchar value 'N/A' to data type int." when it is executed. I thnk error came from else portion coz it works if I change else portion to 'test'.

case when col1 IS NOT NULL then 'N/A'
when GDate IS NULL then '0'
else (SELECT CASE WHEN RDate <> NULL THEN DATEDIFF(dd,GDate,RDate)- COUNT(*)
ELSE DATEDIFF(dd,GDate,getdate())- COUNT(*)
END AS WeekDays
FROM WeekEndsAndHolidays2010
WHERE DayOfWeekDate BETWEEN GDate AND Getdate())
end as NoDays

quote:
Originally posted by Idera

case when col1 IS NULL then 'AA'
when col2 IS NULL then 'BB'
else 'CC' end As test

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 00:59:02

quote:
Originally posted by kwikwisi

Solved .

quote:
Originally posted by kwikwisi

Thanks.
Below is my some portion of query. It gives error
"Conversion failed when converting the varchar value 'N/A' to data type int." when it is executed. I thnk error came from else portion coz it works if I change else portion to 'test'.

case when col1 IS NOT NULL then 'N/A'
when GDate IS NULL then '0'
else (SELECT CASE WHEN RDate <> NULL THEN DATEDIFF(dd,GDate,RDate)- COUNT(*)
ELSE DATEDIFF(dd,GDate,getdate())- COUNT(*)
END AS WeekDays
FROM WeekEndsAndHolidays2010
WHERE DayOfWeekDate BETWEEN GDate AND Getdate())
end as NoDays

quote:
Originally posted by Idera

case when col1 IS NULL then 'AA'
when col2 IS NULL then 'BB'
else 'CC' end As test

PBUH

Is the above your final working query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-18 : 01:16:33

<<
Solved
>>

How?

Madhivanan

Failing to plan is Planning to fail

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-18 : 01:22:42

I just converted to varchar and it worked.

case when col1 IS NOT NULL then 'N/A'
when GDate IS NULL then '0'
else (SELECT CASE WHEN RDate <> NULL THEN convert(varchar(5),DATEDIFF(dd,GDate,RDate)- COUNT(*))
ELSE convert(varchar(5),DATEDIFF(dd,GDate,getdate())- COUNT(*)
END AS WeekDays
FROM WeekEndsAndHolidays2010
WHERE DayOfWeekDate BETWEEN GDate AND Getdate())
end as NoDays

quote:
Originally posted by madhivanan

<<
Solved
>>

How?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 01:28:52

quote:
Originally posted by kwikwisi

I just converted to varchar and it worked.

case when col1 IS NOT NULL then 'N/A'
when GDate IS NULL then '0'
else (SELECT CASE WHEN RDate <> NULL THEN convert(varchar(5),DATEDIFF(dd,GDate,RDate)- COUNT(*))
ELSE convert(varchar(5),DATEDIFF(dd,GDate,getdate())- COUNT(*)
END AS WeekDays
FROM WeekEndsAndHolidays2010
WHERE DayOfWeekDate BETWEEN GDate AND Getdate())
end as NoDays
quote:
Originally posted by madhivanan

<<
Solved
>>

How?

Madhivanan

Failing to plan is Planning to fail

Still its not fully correct
Under default settings CASE WHEN RDate <> NULL wont work
you need to use CASE WHEN RDate IS NOT NULL instead
Also I cant understand why you are so inclined to return WeekDays value as a varchar. According to me, it should be int by all means

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-18 : 01:32:55

I alrdy changed <> to IS NOT.
Correct . Weekdays shld be int but it throws error as i mentioned above if I dont convert it to varchar. I also dont know why...

quote:
Originally posted by visakh16

quote:
Originally posted by kwikwisi

I just converted to varchar and it worked.

case when col1 IS NOT NULL then 'N/A'
when GDate IS NULL then '0'
else (SELECT CASE WHEN RDate <> NULL THEN convert(varchar(5),DATEDIFF(dd,GDate,RDate)- COUNT(*))
ELSE convert(varchar(5),DATEDIFF(dd,GDate,getdate())- COUNT(*)
END AS WeekDays
FROM WeekEndsAndHolidays2010
WHERE DayOfWeekDate BETWEEN GDate AND Getdate())
end as NoDays
quote:
Originally posted by madhivanan

<<
Solved
>>

How?

Madhivanan

Failing to plan is Planning to fail

Still its not fully correct
Under default settings CASE WHEN RDate <> NULL wont work
you need to use CASE WHEN RDate IS NOT NULL instead
Also I cant understand why you are so inclined to return WeekDays value as a varchar. According to me, it should be int by all means

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 01:36:42

quote:
Originally posted by kwikwisi

I alrdy changed <> to IS NOT.
Correct . Weekdays shld be int but it throws error as i mentioned above if I dont convert it to varchar. I also dont know why...

quote:
Originally posted by visakh16

quote:
Originally posted by kwikwisi

I just converted to varchar and it worked.

case when col1 IS NOT NULL then 'N/A'
when GDate IS NULL then '0'
else (SELECT CASE WHEN RDate <> NULL THEN convert(varchar(5),DATEDIFF(dd,GDate,RDate)- COUNT(*))
ELSE convert(varchar(5),DATEDIFF(dd,GDate,getdate())- COUNT(*)
END AS WeekDays
FROM WeekEndsAndHolidays2010
WHERE DayOfWeekDate BETWEEN GDate AND Getdate())
end as NoDays
quote:
Originally posted by madhivanan

<<
Solved
>>

How?

Madhivanan

Failing to plan is Planning to fail

Still its not fully correct
Under default settings CASE WHEN RDate <> NULL wont work
you need to use CASE WHEN RDate IS NOT NULL instead
Also I cant understand why you are so inclined to return WeekDays value as a varchar. According to me, it should be int by all means

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

it wont if you refrain from decorating the first 0 with '' and you dont put 'N/A' as one of returning values. isnt it enough to return null or some other value (like -1) in that case and replace it with N/A in front end while displaying?

See


case when col1 IS NOT NULL then -1
when GDate IS NULL then 0
else (SELECT CASE WHEN RDate <> NULL THEN DATEDIFF(dd,GDate,RDate)- COUNT(*)
ELSE DATEDIFF(dd,GDate,getdate())- COUNT(*)
END AS WeekDays 
FROM WeekEndsAndHolidays2010
WHERE DayOfWeekDate BETWEEN GDate AND Getdate())
end as NoDays

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources