NOOB question with Case statement

Question

NOOB question with Case statement

nrdroque

Old Hand

Points: 348
More actions
October 17, 2019 at 9:34 am

#3689580
hi
here i am again with a noob question again:
i'm trying to do this:
```
case 
when columnA <> 0 
then columnB = 1 
else columnB
end
```
but it return error.
i used case statment before but im the same column, is it possible to use in multiple column with the argument based on one column and the value returned on another?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

SELECTCASE 
WHEN columnA <> 0 THEN 1 
ELSE columnB
END AS columnB
FROM MyTable;

John

DesNorton SSC-Insane Points: 24386 More actions · Answer 2

nrdroque wrote:

hi
here i am again with a noob question again:
i'm trying to do this:
case 
when columnA <> 0 
then columnB = 1 
else columnB
end
but it return error.
i used case statment before but im the same column, is it possible to use in multiple column with the argument based on one column and the value returned on another?

This looks like you are trying to do an UPDATE. If so, then

UPDATE  YourTable
SET     columnB = CASE
                    WHEN columnA <> 0 THEN 1
                    ELSE columnB
                  END;

How to post data/code on a forum to get the best help.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

DesNorton wrote:

This looks like you are trying to do an UPDATE. If so, then

UPDATE  YourTable
SET     columnB = CASE
                    WHEN columnA <> 0 THEN 1
                    ELSE columnB
                  END;

if i'm reading this right - the case is irrelevant (i'm know to be thick after 5pm)

update yourtable set columnB=1 where columnA<>0

no point setting columb = columb

and less rows affected

but if I got that wrong then I apologise

MVDBA

DesNorton SSC-Insane Points: 24386 More actions · Answer 4

MVDBA (Mike Vessey) wrote:

DesNorton wrote:
This looks like you are trying to do an UPDATE. If so, then
UPDATE  YourTable
SET     columnB = CASE
                    WHEN columnA <> 0 THEN 1
                    ELSE columnB
                  END;
if i'm reading this right - the case is irrelevant (i'm know to be thick after 5pm)
update yourtable set columnB=1 where columnA<>0
no point setting columb = columb
and less rows affected
but if I got that wrong then I apologise

You are correct. The UPDATE is an assumption on my part, based on the way that the original code is structured. So I showed it as a case statement in case it was in fact for a select

How to post data/code on a forum to get the best help.

drew.allen SSC Guru Points: 76985 More actions · Answer 5

MVDBA (Mike Vessey) wrote:

DesNorton wrote:
This looks like you are trying to do an UPDATE. If so, then
UPDATE  YourTable
SET     columnB = CASE
                    WHEN columnA <> 0 THEN 1
                    ELSE columnB
                  END;
if i'm reading this right - the case is irrelevant (i'm know to be thick after 5pm)
update yourtable set columnB=1 where columnA<>0
no point setting columb = columb
and less rows affected
but if I got that wrong then I apologise

This represents Des' interpretation of what the OP is trying to do. We don't know if Des' interpretation is both accurate and complete, so we don't know whether your simplification is applicable. The bigger picture may require updating another column even though columb is not being updated.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

You want the best help, post the full SQL code and the error message you are getting. Posting a tiny snippet does not provide the context needed to really help.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

case
when columnA <> 0
then columnB = 1
else columnB
end

The main thing to understand about CASE is that every result from a CASE must be a single value. The expression leading to that single value can be as complex as you need it to be, but it must yield a single value.

Keywords, object names, column names, etc., are not allowed as the result of a CASE statement.

For example, these are not allowed:

SELECT CASE WHEN A = 4 THEN col1, col2, col3 ELSE col2, col3, col4 END

UPDATE ... SET CASE WHEN @col = 'A' THEN A ELSE B END

SELECT ... CASE WHEN @sort = 'ALL' THEN ORDER BY col1 END

Something like this would be allowed:

SELECT CASE WHEN @option = 1 THEN 12 / 4 * 1.5 * SQRT(17) + (SELECT TOP (1) decimal_value FROM dbo.some_table WHERE some_col = 'some_value') ELSE 0 END

The expression is complex, but ultimately it evaluates down to a single value.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

drew.allen wrote:

MVDBA (Mike Vessey) wrote:
DesNorton wrote:
This looks like you are trying to do an UPDATE. If so, then
UPDATE  YourTable
SET     columnB = CASE
                    WHEN columnA <> 0 THEN 1
                    ELSE columnB
                  END;
if i'm reading this right - the case is irrelevant (i'm know to be thick after 5pm)
update yourtable set columnB=1 where columnA<>0
no point setting columb = columb
and less rows affected
but if I got that wrong then I apologise
This represents Des' interpretation of what the OP is trying to do. We don't know if Des' interpretation is both accurate and complete, so we don't know whether your simplification is applicable. The bigger picture may require updating another column even though columb is not being updated.
Drew

hey, i offered a solution based on limited info. I'm happy if this gets the op talking to us all ??

MVDBA

nrdroque Old Hand Points: 348 More actions · Answer 9

Let me try to explain a little better.

123

I need to get a select where all columnB that are not columnA value 1 turn to zero.

DesNorton SSC-Insane Points: 24386 More actions · Answer 10

nrdroque wrote:

Let me try to explain a little better.
I need to get a select where all columnB that are not columnA value 1 turn to zero.

Please provide your expected result. This will assist us to understand what you are trying to achieve.

How to post data/code on a forum to get the best help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

nrdroque wrote:

Let me try to explain a little better.
I need to get a select where all columnB that are not columnA value 1 turn to zero.

This explanation does not match your original post. Again, post your entire code that you currently have, sample data, and expected results.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 12

so

select columnA, case when column a<>1 then columnb else 0 end as colb from mytable

but your spreadsheet results don't match what you are asking - maybe it's a language barrier - may I suggest that you put in your results - columna , columnb and a final column - expected result)

MVDBA