July 24, 2009 at 11:37 am
Good day all,
trying to track down why this simple CASE statement
Will not play ball. The syntax is below:
SELECT
CASE WHEN (Right(MyField,2)*1)=7 THEN MyField02= '999' END,
CASE WHEN (Right(MyField,2)*1)<>7 THEN MyField02= '888' END
FROM MyTable
GO
and the error message is as follows:
Line 2: Incorrect syntax near '='.
Can anyone please point out the mistake.. this is driving me insane !!
Thanks in advance,
Mitch...
July 24, 2009 at 11:40 am
You don't need the MyField = in the THEN portion you just need the value.
SELECT
CASE WHEN (Right(MyField,2)*1)=7 THEN '999' END,
CASE WHEN (Right(MyField,2)*1)7 THEN '888' END
FROM MyTable
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 24, 2009 at 11:43 am
Mitch2007 (7/24/2009)
Good day all,trying to track down why this simple CASE statement
Will not play ball. The syntax is below:
SELECT
CASE WHEN (Right(MyField,2)*1)=7 THEN MyField02= '999' END,
CASE WHEN (Right(MyField,2)*1)7 THEN MyField02= '888' END
FROM MyTable
GO
and the error message is as follows:
Line 2: Incorrect syntax near '='.
Can anyone please point out the mistake.. this is driving me insane !!
Thanks in advance,
Mitch...
OR, you may have meant this:
SELECT
CASE WHEN (Right(MyField,2)*1) = 7 THEN '999'
ELSE '888'
END as MyField02
FROM
MyTable
July 24, 2009 at 12:11 pm
Lynn, yours is not equivalent if MyField can be null.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2009 at 12:20 pm
GilaMonster (7/24/2009)
Lynn, yours is not equivalent if MyField can be null.
True. If that is the case, then we need this:
SELECT
CASE WHEN (Right(MyField,2)*1) = 7
THEN '999'
WHEN (Right(MyField,2)*1) 7
THEN '888'
ELSE null
END as MyField02
FROM
MyTable;
July 24, 2009 at 12:34 pm
Thanks guys.
The code works but what I am trying to do is change the value of MyField02 which relies on the value of MyField. So if MyField is 7 then MyField02 = 999.
I tried to use the code you gave me in order to do this but no joy.
Thanks again,
Mitch....
July 24, 2009 at 12:45 pm
Are you trying to change the value of another column in the table? If so, you need the UPDATE statement.
If not, can you give us some sample data and what you expect to be returned?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2009 at 12:48 pm
So are trying to update the data in the table or trying to output a column named myfield02 in a result set?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 24, 2009 at 12:58 pm
Sorry for the confusion, but I am trying to poutput a new field titled MyField02 which value relies on the value of the first field.
Thanks again for all your assistance, I really appreciate it.
July 24, 2009 at 1:05 pm
How about provided the DDL for the table, sample data for the table (in a readily consummable format that can be cut/paste/run), and expected results based on the sample data. this will help make it clearer what you are trying to accomplish.
Please read the first article I reference below in my signature block regarding asking for assistance. The guidelines it provides for posting questions like this will help you get better answers.
July 24, 2009 at 1:09 pm
Either of these should work, depends on your preference for placement of aliases:
SELECT
MyField02= CASE
WHEN (Right(MyField,2)*1)=7 THEN '999'
WHEN (Right(MyField,2)*1)7 THEN '888'
ELSE NULL
END
FROM
MyTable
SELECT
CASE
WHEN (Right(MyField,2)*1)=7 THEN '999'
WHEN (Right(MyField,2)*1)7 THEN '888'
ELSE NULL
END AS MyField02
FROM
MyTable
You can add as many WHEN's as you have conditions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 24, 2009 at 1:13 pm
Jack Corbett (7/24/2009)
Either of these should work, depends on your preference for placement of aliases:
SELECT
MyField02= CASE
WHEN (Right(MyField,2)*1)=7 THEN '999'
WHEN (Right(MyField,2)*1)7 THEN '888'
ELSE NULL
END
FROM
MyTable
SELECT
CASE
WHEN (Right(MyField,2)*1)=7 THEN '999'
WHEN (Right(MyField,2)*1)7 THEN '888'
ELSE NULL
END AS MyField02
FROM
MyTable
You can add as many WHEN's as you have conditions.
Just remember that the first option shown above for aliases has been depreciated and may no longer be supported in future versions of SQL Server.
July 24, 2009 at 1:23 pm
Huh, I didn't know that "ColumnName = " was deprecated. I know people who really prefer that method. I NEVER use it, I ALWAYS use the seconf "AS ColumnName". Just posted both because Mitch had "ColumnName = " in his example.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 24, 2009 at 1:30 pm
Jack Corbett (7/24/2009)
Huh, I didn't know that "ColumnName = " was deprecated. I know people who really prefer that method. I NEVER use it, I ALWAYS use the seconf "AS ColumnName". Just posted both because Mitch had "ColumnName = " in his example.
I was double checking and I am partially correct. What you posted is still valid, but you can't enclose the column name in quotation marks.
July 24, 2009 at 5:17 pm
Thanks again everyone, got it to display exactly how I wanted.
Cheers !!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply