Alternate way - 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

General SQL Server Forums

New to SQL Server Programming

Alternate way

Author

Topic

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-01-29 : 03:16:46

Hi all!

This is my sql statment that works perfectly

select name,id,case when maths>=35 then 'pass' else 'fail' end as maths,
case when science>=35 then 'pass' else 'fail' end as science,
case when tamil>=35 then 'pass' else 'fail' end as tamil ,
case when english>=35 then 'pass' else 'fail' end as english,
case when social>=35 then 'pass' else 'fail' end as social
from student

I dont want to use case in my querey? Is there any way can i replace
case with something?
Thanks in advance!

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-29 : 03:36:02

Why you don't want to use case ? Any specific reason for this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 03:45:31

or use just select and let your front end do it

Madhivanan

Failing to plan is Planning to fail

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-01-29 : 03:48:59

quote:
Originally posted by sunil

Why you don't want to use case ? Any specific reason for this.

Thanks for the reply.
There is no reason behind it.
Is it possible can i do it in sql?I'm using sql 2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 03:52:59

CASE statement is a perfectly valid T-SQL command. Also available in SQL Server 2000.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 03:58:52

Here is an alternative to NOT use CASE. I hope you enjoy it

SELECT		Yak.Name,
		Yak.ID,
		MAX(Yak.Maths) AS Maths,
		MAX(Yak.Science) AS Science,
		MAX(Yak.Tamil) AS Tamil,
		MAX(Yak.English) AS English,
		MAX(Yak.Social) AS Social
FROM		(
			SELECT	Name,
				ID,
				'Fail' AS Maths,
				'Fail' AS Science,
				'Fail' AS Tamil,
				'Fail' AS English,
				'Fail' AS Social
			FROM	Student

			UNION ALL

			SELECT	Name,
				ID,
				'Pass',
				'Fail',
				'Fail',
				'Fail',
				'Fail'
			FROM	Student
			WHERE	Maths > 34

			UNION ALL

			SELECT	Name,
				ID,
				'Fail',
				'Pass',
				'Fail',
				'Fail',
				'Fail'
			FROM	Student
			WHERE	Science > 34

			UNION ALL

			SELECT	Name,
				ID,
				'Fail',
				'Fail',
				'Pass',
				'Fail',
				'Fail'
			FROM	Student
			WHERE	Tamil > 34

			UNION ALL

			SELECT	Name,
				ID,
				'Fail',
				'Fail',
				'Fail',
				'Pass',
				'Fail'
			FROM	Student
			WHERE	English > 34

			UNION ALL

			SELECT	Name,
				ID,
				'Fail',
				'Fail',
				'Fail',
				'Fail',
				'Pass'
			FROM	Student
			WHERE	Social > 34
		) AS Yak
GROUP BY	Yak.Name,
		Yak.ID

E 12°55'05.25"
N 56°04'39.16"

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-01-29 : 04:00:05

quote:
Originally posted by Peso

CASE statement is a perfectly valid T-SQL command. Also available in SQL Server 2000.

E 12°55'05.25"
N 56°04'39.16"

Thank you Peso.
I know case is perfectly a valid statment in sql 2000.I just want to
know shall i replace case with anything in my expression?Is it possible or not?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 04:01:39

No, you should not replace the CASE.
Yes it is possible. See my previous answer.

E 12°55'05.25"
N 56°04'39.16"

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-01-29 : 04:21:00

quote:

Originally posted by Peso

Here is an alternative to NOT use CASE. I hope you enjoy it

SELECT		Yak.Name,
		Yak.ID,
		MAX(Yak.Maths) AS Maths,
		MAX(Yak.Science) AS Science,
		MAX(Yak.Tamil) AS Tamil,
		MAX(Yak.English) AS English,
		MAX(Yak.Social) AS Social
FROM		(
			SELECT	Name,
				ID,
				'Fail' AS Maths,
				'Fail' AS Science,
				'Fail' AS Tamil,
				'Fail' AS English,
				'Fail' AS Social
			FROM	Student

			UNION ALL

			SELECT	Name,
				ID,
				'Pass',
				'Fail',
				'Fail',
				'Fail',
				'Fail'
			FROM	Student
			WHERE	Maths > 34

			UNION ALL

			SELECT	Name,
				ID,
				'Fail',
				'Pass',
				'Fail',
				'Fail',
				'Fail'
			FROM	Student
			WHERE	Science > 34

			UNION ALL

			SELECT	Name,
				ID,
				'Fail',
				'Fail',
				'Pass',
				'Fail',
				'Fail'
			FROM	Student
			WHERE	Tamil > 34

			UNION ALL

			SELECT	Name,
				ID,
				'Fail',
				'Fail',
				'Fail',
				'Pass',
				'Fail'
			FROM	Student
			WHERE	English > 34

			UNION ALL

			SELECT	Name,
				ID,
				'Fail',
				'Fail',
				'Fail',
				'Fail',
				'Pass'
			FROM	Student
			WHERE	Social > 34
		) AS Yak
GROUP BY	Yak.Name,
		Yak.ID

E 12°55'05.25"
N 56°04'39.16"

I'm really happy to see your reply.Thank you so mcuh Peso!
If number of column increases then the combiantion of Pass or fail will also increases.So case is the better choice here.
It's a very good example.Thanks once again!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 05:46:14

Dont overcomplicate the things than expected

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources