Advance Uses Of Case Statements

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

SQL Server 2000 Forums

Transact-SQL (2000)

Advance Uses Of Case Statements

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-18 : 09:59:57

Mike writes "Is there any way to use a case statement to manipulate some columns in CASE 1, other columns in Case 2 and still others in Case 3. For example:

Case
When Criteria1
Column1 * 20,
Column2 * 30
When Criteria2
Column2 * 30,
Column3 * 30,
Column4 * 10
When Criteria3
Column1 * 10,
Column2 * 20,
Column3 * 30,
Column4 * 40
End

Any help or insite into a soluion would be greatly appreciated.

Mike"

Jay99

468 Posts

Posted - 2002-03-18 : 11:11:37

sure . .


update myTable
set
column1 = case
    when criteria1 then column1 * 20
    when criteria3 then column1 * 10
    else column1 end,
column2 = case
    when criteria1 then column2 * 30
    when criteria2 then column2 * 30
    else column2 end,
column3 = ...
column4 = ...

Jay

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-03-25 : 11:48:28

>> Is there any way to use a case statement to manipulate some columns .. <<

Totally impossible!! Why? Because there is no CASE statement in SQL; it is a CASE **expression**!! Read the manual! Learn the language! Collect the trading cards!

An expression must return (1) One and only value or a NULL (2) That value must be one and only one datatype (otherwise, you could have a mixed column in a table).

--CELKO--
Joe Celko, SQL Guru

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-03-25 : 13:15:36

Are you the real celko or just an immitation?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-03-25 : 15:23:43

>> Are you the real celko or just an immitation? <<

The real one -- I guess <g>.

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources