Are IN statements allowed in CASEs..? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Are IN statements allowed in CASEs..?

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-11-27 : 03:34:37

For example I have
[CODE]

CASE (a.t_id)
WHEN (a.t_id in (22,23,27,30,38))
THEN t.desc
ELSE 'N/A'
END 'Column name..',

[/CODE]

and that is giving me "incorrect syntax near 'in'" ??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 03:38:40

[code]CASE ~~(a.t_id)~~
WHEN (a.t_id in (22,23,27,30,38))
THEN t.desc
ELSE 'N/A'
END 'Column name..',[/code]

KH
[spoiler]Time is always against us[/spoiler]

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-11-27 : 03:39:21

CASE WHEN a.t_id in (22,23,27,30,38)
THEN t.desc
ELSE 'N/A'
END 'Column name..',

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-27 : 03:40:59

This is why I once suggested always use CASE WHEN expression than CASE expression WHEN

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-11-27 : 03:41:54

They are!

The syntax for case is not the same as VB or other front end languages.....

CASE WHEN a.t_id in (22,23,27,30,38) THEN t.desc
ELSE 'N/A'
END as ColumnName

Duane.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 03:45:16

slow internet connections ?

KH
[spoiler]Time is always against us[/spoiler]

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-11-27 : 03:48:02

I live in Africa - we still use the guy that runs with a scroll message for our communications

Duane.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-27 : 10:50:36

Hmmm. you are in the modern part of africa then. The others still use the drums.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-27 : 11:34:27

We say this yet the US is lagging far behind many countries these days in terms of speed:

http://globaltechforum.eiu.com/index.asp?layout=rich_story&doc_id=10982&title=US+lags+behind+leaders+in+Internet+speed&categoryid=28&channelid=4

Future guru in the making.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 11:47:42

quote:
Originally posted by Zoroaster

We say this yet the US is lagging far behind many countries these days in terms of speed:

http://globaltechforum.eiu.com/index.asp?layout=rich_story&doc_id=10982&title=US+lags+behind+leaders+in+Internet+speed&categoryid=28&channelid=4

Future guru in the making.

really ? It's unbelievable. But anyway, most of the sites are located at US. So even with connections of 2Mbps it will be better than access it from this region.

I am now connected to the internet at 3.5 Mbps at home. Guess it is slightly better than the median speed in US.

quote:
Sweden at 18.2 mbps

That's does explain Peso's posting rate

KH
[spoiler]Time is always against us[/spoiler]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-27 : 12:00:57

Always remember: CASE is not a statement in t-sql, it is an EXPRESSION.

- Jeff
http://weblogs.sqlteam.com/JeffS

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-27 : 12:27:12

quote:
Originally posted by khtan

quote:
Originally posted by Zoroaster

We say this yet the US is lagging far behind many countries these days in terms of speed:

http://globaltechforum.eiu.com/index.asp?layout=rich_story&doc_id=10982&title=US+lags+behind+leaders+in+Internet+speed&categoryid=28&channelid=4

Future guru in the making.

really ? It's unbelievable. But anyway, most of the sites are located at US. So even with connections of 2Mbps it will be better than access it from this region.

I am now connected to the internet at 3.5 Mbps at home. Guess it is slightly better than the median speed in US.

quote:
Sweden at 18.2 mbps

That's does explain Peso's posting rate

KH
[spoiler]Time is always against us[/spoiler]

Khtan,I have actually read that Singapore will be among the top - as in this article where they state the investments being made in your country to lay fiber:
http://www.usatoday.com/tech/techinvestor/2004-01-19-broadband_x.htm
Not sure where I read it but I remember the goal was for universal 100MB downloads in Singapore once the fiber network is in place.

Future guru in the making.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 01:22:46

quote:
Originally posted by jsmith8858

Always remember: CASE is not a statement in t-sql, it is an EXPRESSION.

- Jeff
http://weblogs.sqlteam.com/JeffS

I think it is the effect of front end's CASE. I see that lot of people call CASE as statement in VBA, VB,java etc. Also most of the articles regarding CASE specify it as statement(sometimes expression or function). So people easily think that as statement than expression

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-28 : 05:17:07

quote:
Originally posted by pazzy11

For example I have
[CODE]

CASE (a.t_id)
WHEN (a.t_id in (22,23,27,30,38))
THEN t.desc
ELSE 'N/A'
END 'Column name..',

[/CODE]

and that is giving me "incorrect syntax near 'in'" ??

this link will help to clear the problem
http://www.craigsmullins.com/ssu_0899.htm

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-28 : 15:25:11

quote:
Originally posted by arorarahul.0688

quote:
Originally posted by pazzy11

For example I have
[CODE]

CASE (a.t_id)
WHEN (a.t_id in (22,23,27,30,38))
THEN t.desc
ELSE 'N/A'
END 'Column name..',

[/CODE]

and that is giving me "incorrect syntax near 'in'" ??

this link will help to clear the problem
http://www.craigsmullins.com/ssu_0899.htm

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

What do you mean that link will help? The first example is:

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

EXACT SAME SYNTAX AND ERROR AS THE OP WAS TALKING ABOUT.

Their first example is WRONG.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-29 : 09:12:50

This is really bad example. There are even 2 syntax error here.

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

A quick reference to the BOL on the syntax of the CASE
http://msdn2.microsoft.com/en-us/library/ms181765.aspx shows that there are 2 syntax available for CASE. simple and search. There isn't a simple search syntax format.

quote:

Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

KH
[spoiler]Time is always against us[/spoiler]

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-29 : 15:11:56

quote:
Originally posted by khtan

This is really bad example. There are even 2 syntax error here.
SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

A quick reference to the BOL on the syntax of the CASE
http://msdn2.microsoft.com/en-us/library/ms181765.aspx shows that there are 2 syntax available for CASE. simple and search. There isn't a simple search syntax format.
quote:

Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

KH
[spoiler]Time is always against us[/spoiler]

Yea, I can only imagine that the OP was really confussed after seeing the link with the bad example. Please disreguard this link since it contains a bad example. http://www.craigsmullins.com/ssu_0899.htm

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-29 : 23:18:50

Yes. The OP must be very confuse now and don't know what to do or what to ask now. OP has not response at all after the initial post.

Poor OP

KH
[spoiler]Time is always against us[/spoiler]

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-30 : 15:23:54

Hey OP, inCASE you have more questions, feel free to ask...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-30 : 21:54:04

so funny...

I think the sig should be:

IMPOSSIBLE = I+M+POS+SIBLE

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-12-01 : 02:44:35

quote:
Originally posted by jezemine

so funny...

I think the sig should be:

IMPOSSIBLE = I+M+POS+SIBLE

elsasoft.org

is it your attitude?

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources