Case statement Error

Hi,
I don't know what i am doing wrong here, I am always getting this error
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
When I am running the following query
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
Could anyone put some light on it.
Thanks
J S"John Smith" <John@.nospam.yahoo.com> wrote in news:ebqEpwfOFHA.1500
@.TK2MSFTNGP09.phx.gbl:

> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> 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
> Could anyone put some light on it.
> Thanks
> J S
>
>
Leave out the field name between the CASE and WHEN keywords.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin|||There are two versions of Case Syntax, and you are "mixing" them...
Ver 1
Case <Expression>
When <ExpressionValue1> Then <OutValue1>
When <ExpressionValue2> Then <OutValue2>
When <ExpressionValue3> Then <OutValue3>
Else <ElseOutValue> End
Version 2
Case
When <BooleanExpression1> Then <OutValue1>
When <BooleanExpression2> Then <OutValue2>
When <BooleanExpression3> Then <OutValue3>
Else <ElseOutValue> End
So you need to eliminate the "price" right after the Case...
SELECT title, price,
Budget = CASE
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
"John Smith" wrote:

> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> 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
> Could anyone put some light on it.
> Thanks
> J S
>
>|||this should work:
SELECT title, price,
Budget = CASE --price <- removed
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
dean
"John Smith" <John@.nospam.yahoo.com> wrote in message
news:ebqEpwfOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> 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
> Could anyone put some light on it.
> Thanks
> J S
>|||Thanks a lot guys, it is working....
J S
"John Smith" <John@.nospam.yahoo.com> wrote in message
news:ebqEpwfOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> 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
> Could anyone put some light on it.
> Thanks
> J S
>|||I removed price from CASE. This passed syntax check for me:
SELECT title, price,
Budget = CASE
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

Cascading Triggers

Hi,
I have 3 Tables with After Update Triggers.
They all cascade like City -> Province -> State (as a simplified example)
If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.
This works.
However:
If I Set A, a trigger on table City sets B BUT a trigger on table Province
DOESN'T in turn Set C in table State.
So: Individually the Triggers work, but they don't Cascade their action.
Any advise?
TIA,
MichaelHi Michael,
I think I'm having the same problem. I'm hoping to take action via a
trigger on child records when the parent is deleted, but the child table's
trigger doesn't seem to fire. Hopefully someone has a suggestion.
Rgds,
Bill
"Michael Maes" <michael@.merlot.com> wrote in message
news:5BEB7F99-288B-48A8-98DE-F2FBD22AFE75@.microsoft.com...
> Hi,
> I have 3 Tables with After Update Triggers.
> They all cascade like City -> Province -> State (as a simplified example)
> If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
> If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.
> This works.
> However:
> If I Set A, a trigger on table City sets B BUT a trigger on table Province
> DOESN'T in turn Set C in table State.
> So: Individually the Triggers work, but they don't Cascade their action.
> Any advise?
> TIA,
>
> Michael
>|||There is a server option "Allow triggers to be fired which fire other
triggers (nested triggers)". Here is the description copied from BO:
1. Expand a server group.
2. Right-click a server, and then click Properties.
3. Click the Server Settings tab.
4. Under Server behavior, select or clear the Allow triggers to be
fired which fire other triggers (nested triggers) check box.
For more information, see Books Online, article "Using Nested Triggers"|||Hi Sergei,
Thanks for your advise!
I just found an article on this:
http://www.sqlservercentral.com/col.../triggers_1.asp
Nested and Recursive Triggers
Nested triggers are triggers that fire due to actions of other triggers.
For instance, I delete a row from TableA. A trigger on TableA fires to
delete rows from TableB. Because I'm deleting rows from TableB, a trigger
fires on TableB to record the deletes. This is an example of a nested
trigger. As we've talked about, SQL Server 7.0 doesn't support cascading
updates and deletes based on foreign key relationships. Therefore, if we
want to relate our data we can't use DRI and must resort to triggers or some
application oversight. Let's say we've got a cascade delete to fire down 3
or 4 tables. Nested triggers are our answer. Our delete on a particular
table fires a trigger which deletes rows for another table, which fires a
trigger, so on and so forth. SQL Server 7 and 2000 support up to 32 levels
of nested triggers.
Now the big question is, does my SQL Server allow nested triggers? That's
an easy question to answer. It's on by default, but in Query Analyzer we ca
n
issue the following command:
EXEC sp_configure 'nested triggers'
If your run_value is set to 0, your server isn't allowing nested triggers.
If it's set to 1, nested triggers may fire. This is a server wide setting.
Now, to change your setting, once again use the sp_configure command:
To turn off nested triggers:
EXEC sp_configure 'nested triggers', 0
RECONFIGURE
To turn on nested triggers:
EXEC sp_configure 'nested triggers', 1
RECONFIGURE
"Sergei Almazov" wrote:

> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||I wonder why the defualt setting is off for this. (Protecting us from
potentially faulty programming perhaps?) ;-) Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.
In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||I wonder why the defualt setting is off for this. (Protecting us from
potentially faulty programming perhaps?) ;-) Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.
In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||Hi Bill,
What scares me is that someone or something else can disable your nested
triggers because it's DataServer-Wide :-(((
"Bill Hicks" wrote:

> I wonder why the defualt setting is off for this. (Protecting us from
> potentially faulty programming perhaps?) ;-) Also, seems like it would be
> handy to be able to turn it on per table or database, rather than at the
> server level.
> In my case where I'm just trying to perform some actions first with the
> information in detail records that will be deleted, would it be better to
> turn off the cascade delete for the related table and handle deleting of t
he
> detail records in the delete trigger of the master table, or is it better
to
> allow nested triggers?
> "Sergei Almazov" <almazik@.ukr.net> wrote in message
> news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
>
>