what am i doing wrong? 
Author Message
 what am i doing wrong?
here is my code:
SELECT m.myCol1, m.myCol2, m.myCol3,
CASE m.myCol1
 WHEN 1 THEN  m.myCol3 = 'Hello World'
 WHEN 2 THEN  'Column 2'
 WHEN 3 THEN  'Column 3'
 WHEN 4 THEN  'Column 4'
 WHEN 5 THEN  'Column 5'
 ELSE 'Neither'
End AS Coulmn
FROM myTable As m

here is my error:
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '='.

why does m.myCol3 = 'Hello World' not work?



Sat, 30 Apr 2005 03:40:08 GMT
 what am i doing wrong?

I do not believe that you can do m.myCol3 = 'Hello World'. You trying to
assign a literal to a column. Maybe you can just try ...WHEN 1 THEN  'Hello
World'


Quote:
> here is my code:
> SELECT m.myCol1, m.myCol2, m.myCol3,
> CASE m.myCol1
>  WHEN 1 THEN  m.myCol3 = 'Hello World'
>  WHEN 2 THEN  'Column 2'
>  WHEN 3 THEN  'Column 3'
>  WHEN 4 THEN  'Column 4'
>  WHEN 5 THEN  'Column 5'
>  ELSE 'Neither'
> End AS Coulmn
> FROM myTable As m

> here is my error:
> Server: Msg 170, Level 15, State 1, Line 22
> Line 22: Incorrect syntax near '='.

> why does m.myCol3 = 'Hello World' not work?



Sat, 30 Apr 2005 04:58:10 GMT
 what am i doing wrong?
Ok, didnt know it was a rule that you couldn't add a literal in a case.
Guess I have to find another way ( again ) to do what i need to


Quote:
> I do not believe that you can do m.myCol3 = 'Hello World'. You trying to
> assign a literal to a column. Maybe you can just try ...WHEN 1 THEN
'Hello
> World'



> > here is my code:
> > SELECT m.myCol1, m.myCol2, m.myCol3,
> > CASE m.myCol1
> >  WHEN 1 THEN  m.myCol3 = 'Hello World'
> >  WHEN 2 THEN  'Column 2'
> >  WHEN 3 THEN  'Column 3'
> >  WHEN 4 THEN  'Column 4'
> >  WHEN 5 THEN  'Column 5'
> >  ELSE 'Neither'
> > End AS Coulmn
> > FROM myTable As m

> > here is my error:
> > Server: Msg 170, Level 15, State 1, Line 22
> > Line 22: Incorrect syntax near '='.

> > why does m.myCol3 = 'Hello World' not work?



Sat, 30 Apr 2005 04:06:07 GMT
 what am i doing wrong?
btw, thanks for your help


Quote:
> I do not believe that you can do m.myCol3 = 'Hello World'. You trying to
> assign a literal to a column. Maybe you can just try ...WHEN 1 THEN
'Hello
> World'



> > here is my code:
> > SELECT m.myCol1, m.myCol2, m.myCol3,
> > CASE m.myCol1
> >  WHEN 1 THEN  m.myCol3 = 'Hello World'
> >  WHEN 2 THEN  'Column 2'
> >  WHEN 3 THEN  'Column 3'
> >  WHEN 4 THEN  'Column 4'
> >  WHEN 5 THEN  'Column 5'
> >  ELSE 'Neither'
> > End AS Coulmn
> > FROM myTable As m

> > here is my error:
> > Server: Msg 170, Level 15, State 1, Line 22
> > Line 22: Incorrect syntax near '='.

> > why does m.myCol3 = 'Hello World' not work?



Sat, 30 Apr 2005 04:06:21 GMT
 what am i doing wrong?
Here is what I am trying to accomplish, I wish to read in a value from the
current record compare it to a number, and then do some calculations.  lemme
explain with the code.

SELECT m.myCol1, m.myCol2, myCol3,
CASE m.myCol1
 WHEN 1 THEN  m.myCol3 = 'Hello World'    ' If myCol1 is 1 then write 'Hello
World' to myCol3
 WHEN 2 THEN  m.myCol3 = m.myCol2        'If myCol1 is 1 then write myCol2
to myCol3
 WHEN 3 THEN  m.myCol3 = m.myCol1 + 1    'If myCol1 is 1 then add 1 to
myCol1
 WHEN 4 THEN  m.myCol3 = '4th time is the charm'    'If myCol1 is 1 then
write '4th time is the charm'    to myCol3
 WHEN 5 THEN  m.myCol3 = 'Good Grief'        'If myCol1 is 1 then write Good
Grief' to myCol3
 ELSE 'Neither'
End
FROM myTable As m

I keep getting an error about the equals sign.  Is this even possible to do
with a Case?



Sat, 30 Apr 2005 05:06:08 GMT
 what am i doing wrong?

Quote:
>  WHEN 1 THEN  m.myCol3 = 'Hello World'    ' If myCol1 is 1 then write
'Hello
> World' to myCol3

Are you trying to perform a SELECT or an UPDATE?


Sat, 30 Apr 2005 05:16:26 GMT
 what am i doing wrong?
Jason,

What do you expect the output to look like?  The third column of the
output is myCol3, yet you seem to be trying to do some sort of
assignment to myCol3 as the fourth column of the result set.  Is
myCol3 a column in the table?  Are you trying to update the table, or
just change the result set?

Roy

Quote:

>Here is what I am trying to accomplish, I wish to read in a value from the
>current record compare it to a number, and then do some calculations.  lemme
>explain with the code.

>SELECT m.myCol1, m.myCol2, myCol3,
>CASE m.myCol1
> WHEN 1 THEN  m.myCol3 = 'Hello World'    ' If myCol1 is 1 then write 'Hello
>World' to myCol3
> WHEN 2 THEN  m.myCol3 = m.myCol2        'If myCol1 is 1 then write myCol2
>to myCol3
> WHEN 3 THEN  m.myCol3 = m.myCol1 + 1    'If myCol1 is 1 then add 1 to
>myCol1
> WHEN 4 THEN  m.myCol3 = '4th time is the charm'    'If myCol1 is 1 then
>write '4th time is the charm'    to myCol3
> WHEN 5 THEN  m.myCol3 = 'Good Grief'        'If myCol1 is 1 then write Good
>Grief' to myCol3
> ELSE 'Neither'
>End
>FROM myTable As m

>I keep getting an error about the equals sign.  Is this even possible to do
>with a Case?



Sat, 30 Apr 2005 05:30:02 GMT
 what am i doing wrong?
Jason,

  The problem is with the first alternative in the case expression.
m.myCol3 = 'Hello World'   is not a valid T-SQL expression.

If myCol1 equals 1, then your query becomes

select m.myCol1, m.myCol2, m.myCol3, (m.myCol3 = 'Hello World') as Coulmn
from myTable as m

which doesn't parse.  I'm not sure what you are trying to do, so I
can't say more.

Steve Kass
Drew University

Quote:

>here is my code:
>SELECT m.myCol1, m.myCol2, m.myCol3,
>CASE m.myCol1
> WHEN 1 THEN  m.myCol3 = 'Hello World'
> WHEN 2 THEN  'Column 2'
> WHEN 3 THEN  'Column 3'
> WHEN 4 THEN  'Column 4'
> WHEN 5 THEN  'Column 5'
> ELSE 'Neither'
>End AS Coulmn
>FROM myTable As m

>here is my error:
>Server: Msg 170, Level 15, State 1, Line 22
>Line 22: Incorrect syntax near '='.

>why does m.myCol3 = 'Hello World' not work?



Sat, 30 Apr 2005 03:58:09 GMT
 what am i doing wrong?
The code should be:

WHEN 1 THEN  'Hello World'

You can't change the value of another column within a case
statement.  You can use values from other columns to
derive the value for the current 'column':

WHEN 1 THEN  m.myCol3
WHEN 2 then m.myCol2
etc

HTH
Cary

Quote:
>-----Original Message-----
>here is my code:
>SELECT m.myCol1, m.myCol2, m.myCol3,
>CASE m.myCol1
> WHEN 1 THEN  m.myCol3 = 'Hello World'
> WHEN 2 THEN  'Column 2'
> WHEN 3 THEN  'Column 3'
> WHEN 4 THEN  'Column 4'
> WHEN 5 THEN  'Column 5'
> ELSE 'Neither'
>End AS Coulmn
>FROM myTable As m

>here is my error:
>Server: Msg 170, Level 15, State 1, Line 22
>Line 22: Incorrect syntax near '='.

>why does m.myCol3 = 'Hello World' not work?

>.



Sat, 30 Apr 2005 03:55:35 GMT
 what am i doing wrong?

Quote:
> Guess I have to find another way ( again ) to do what i need to

Perhaps if you describe exactly what you need to do, instead of just showing
the code that you're trying, you will get more useful assistance?

--
Aaron Bertrand, MVP
http://www.aspfaq.com/

FREE server diagnostics component:
http://www.perfhound.com/



Sat, 30 Apr 2005 04:10:57 GMT
 what am i doing wrong?
Jason,

  You can't update column values in a select statement, and in addition,
the alternatives
in a CASE statement must be expressions, not statements.  You can do
what you might be
asking with an update statement:

update myTable set
  myCol3 = case myCol1
         when 1 then 'Hello World'
         when 2 then myCol2
         when 3 then myCol1 + 1
         when 4 then '4th time is the charm'
         when 5 then 'Good Grief'
         else ??? end
-- not clear what you want to do if myCol1 isn't 1, 2, 4, or 5
-- if you want to put 'Neither'  in myCol3, use 'Neither' where ??? is
-- if you want to leave myCol3 alone, remove "else ???" and add
-- WHERE myCol1 in (1,2,3,4,5)  to the query

SK

Quote:

>Here is what I am trying to accomplish, I wish to read in a value from the
>current record compare it to a number, and then do some calculations.  lemme
>explain with the code.

>SELECT m.myCol1, m.myCol2, myCol3,
>CASE m.myCol1
> WHEN 1 THEN  m.myCol3 = 'Hello World'    ' If myCol1 is 1 then write 'Hello
>World' to myCol3
> WHEN 2 THEN  m.myCol3 = m.myCol2        'If myCol1 is 1 then write myCol2
>to myCol3
> WHEN 3 THEN  m.myCol3 = m.myCol1 + 1    'If myCol1 is 1 then add 1 to
>myCol1
> WHEN 4 THEN  m.myCol3 = '4th time is the charm'    'If myCol1 is 1 then
>write '4th time is the charm'    to myCol3
> WHEN 5 THEN  m.myCol3 = 'Good Grief'        'If myCol1 is 1 then write Good
>Grief' to myCol3
> ELSE 'Neither'
>End
>FROM myTable As m

>I keep getting an error about the equals sign.  Is this even possible to do
>with a Case?



Sat, 30 Apr 2005 05:39:33 GMT
 what am i doing wrong?
Thanks Steve, you have pointed me in the right direction.  I basically have
a table that with 3 columns (col1, col2, col3).  col1 contains level number
for its place on a tree.  col2 is some title.  col3 title of the parent
node.  Like a row has: "4, title, title For 3".  The previous row has: "3,
title For 3, title For 2".  The next row after '4' could be: "4, title#2,
title For 3" Or "5, title For 5, title For 4".  And I'm trying to figure
this out programatically.  I thought maybe I could do that with a case
statement.  But I hope maybe what I'm trying to do makes a little more
sense.


Quote:
> Jason,

>   You can't update column values in a select statement, and in addition,
> the alternatives
> in a CASE statement must be expressions, not statements.  You can do
> what you might be
> asking with an update statement:

> update myTable set
>   myCol3 = case myCol1
>          when 1 then 'Hello World'
>          when 2 then myCol2
>          when 3 then myCol1 + 1
>          when 4 then '4th time is the charm'
>          when 5 then 'Good Grief'
>          else ??? end
> -- not clear what you want to do if myCol1 isn't 1, 2, 4, or 5
> -- if you want to put 'Neither'  in myCol3, use 'Neither' where ??? is
> -- if you want to leave myCol3 alone, remove "else ???" and add
> -- WHERE myCol1 in (1,2,3,4,5)  to the query

> SK


> >Here is what I am trying to accomplish, I wish to read in a value from
the
> >current record compare it to a number, and then do some calculations.
lemme
> >explain with the code.

> >SELECT m.myCol1, m.myCol2, myCol3,
> >CASE m.myCol1
> > WHEN 1 THEN  m.myCol3 = 'Hello World'    ' If myCol1 is 1 then write
'Hello
> >World' to myCol3
> > WHEN 2 THEN  m.myCol3 = m.myCol2        'If myCol1 is 1 then write
myCol2
> >to myCol3
> > WHEN 3 THEN  m.myCol3 = m.myCol1 + 1    'If myCol1 is 1 then add 1 to
> >myCol1
> > WHEN 4 THEN  m.myCol3 = '4th time is the charm'    'If myCol1 is 1 then
> >write '4th time is the charm'    to myCol3
> > WHEN 5 THEN  m.myCol3 = 'Good Grief'        'If myCol1 is 1 then write
Good
> >Grief' to myCol3
> > ELSE 'Neither'
> >End
> >FROM myTable As m

> >I keep getting an error about the equals sign.  Is this even possible to
do
> >with a Case?



Sat, 30 Apr 2005 21:07:29 GMT
 what am i doing wrong?

Jason,

  You might read the Books Online section on "Expanding Hierarchies," or
look at
some books covering this.  But I think you still haven't made it clear
what you want
the query to return.  A select query will return a set or rows from the
table.  You
surely don't want just select * from T, so just what do you want?  Do
you have
a particular row in mind for which you want to retrieve related rows?
 If you do,

case you'll
have something  like

select * from myTable
where

or

or

SK

Quote:

>Thanks Steve, you have pointed me in the right direction.  I basically have
>a table that with 3 columns (col1, col2, col3).  col1 contains level number
>for its place on a tree.  col2 is some title.  col3 title of the parent
>node.  Like a row has: "4, title, title For 3".  The previous row has: "3,
>title For 3, title For 2".  The next row after '4' could be: "4, title#2,
>title For 3" Or "5, title For 5, title For 4".  And I'm trying to figure
>this out programatically.  I thought maybe I could do that with a case
>statement.  But I hope maybe what I'm trying to do makes a little more
>sense.



>>Jason,

>>  You can't update column values in a select statement, and in addition,
>>the alternatives
>>in a CASE statement must be expressions, not statements.  You can do
>>what you might be
>>asking with an update statement:

>>update myTable set
>>  myCol3 = case myCol1
>>         when 1 then 'Hello World'
>>         when 2 then myCol2
>>         when 3 then myCol1 + 1
>>         when 4 then '4th time is the charm'
>>         when 5 then 'Good Grief'
>>         else ??? end
>>-- not clear what you want to do if myCol1 isn't 1, 2, 4, or 5
>>-- if you want to put 'Neither'  in myCol3, use 'Neither' where ??? is
>>-- if you want to leave myCol3 alone, remove "else ???" and add
>>-- WHERE myCol1 in (1,2,3,4,5)  to the query

>>SK


>>>Here is what I am trying to accomplish, I wish to read in a value from

>the

>>>current record compare it to a number, and then do some calculations.

>lemme

>>>explain with the code.

>>>SELECT m.myCol1, m.myCol2, myCol3,
>>>CASE m.myCol1
>>>WHEN 1 THEN  m.myCol3 = 'Hello World'    ' If myCol1 is 1 then write

>'Hello

>>>World' to myCol3
>>>WHEN 2 THEN  m.myCol3 = m.myCol2        'If myCol1 is 1 then write

>myCol2

>>>to myCol3
>>>WHEN 3 THEN  m.myCol3 = m.myCol1 + 1    'If myCol1 is 1 then add 1 to
>>>myCol1
>>>WHEN 4 THEN  m.myCol3 = '4th time is the charm'    'If myCol1 is 1 then
>>>write '4th time is the charm'    to myCol3
>>>WHEN 5 THEN  m.myCol3 = 'Good Grief'        'If myCol1 is 1 then write

>Good

>>>Grief' to myCol3
>>>ELSE 'Neither'
>>>End

>>>FROM myTable As m

>>>I keep getting an error about the equals sign.  Is this even possible to

>do

>>>with a Case?



Sat, 30 Apr 2005 22:31:38 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. what am I doing wrong in this procedure?

2. TSQL question - what am I doing wrong?

3. Simple one what am i doing wrong

4. Darn what am i doing wrong?

5. What am I doing wrong

6. What am I doing wrong?

7. what am I doing wrong?

8. What am I doing wrong...

9. What am I doing wrong with this SP??

10. Parameter Problem - What am I doing wrong?

11. What in the world am I doing wrong?

12. Another date problem - What am I doing wrong?


 
Powered by phpBB® Forum Software