?
Solved

How to use multiple conditions using when then?

Posted on 2011-03-23
5
Medium Priority
?
598 Views
Last Modified: 2012-05-11
I am trying to update a field.
The field should be updated only if the current month is x and field_04 contains value y.
When I use a single condition the field gets updated just fine.
but I can't seem to get it to function when I add another condition.

This works:

update Items
set Field_04 = case
      when  DATEPART(mm,getdate()) = 1 and Field_04<'3500' then '3500'
      when  DATEPART(mm,getdate()) = 2 and Field_04<'4500' then '4500'
       end
from Items
where DATEPART(mm,getdate()) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) and Condition='A'


This doesn't work:

update Items
set Field_04 = case
      when  DATEPART(mm,getdate()) = 1 then '3500'
      when  DATEPART(mm,getdate()) = 2 then '4500'
       end
from Items
where DATEPART(mm,getdate()) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) and Condition='A'


I guess it has something to do with the when then statement.
Maybe it cannot contain multiple conditions or maybe I'm just using the wrong syntax?

Thanks in advance!
Clyde.
0
Comment
Question by:Clyde_Radcliffe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 35197465
try this

update Items
set Field_04 = case
      when  MONTH(getdate()) = 1 then '3500'
      when  MONTH(getdate()) = 2 then '4500'
       end
from Items
where MONTH(getdate()) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
  and Condition='A'
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 35197471
You have mixed up the not working and the working solution.
No, I don't see why that should not work. You can concat conditions in the WHEN clause, as you did.
But your where condition does not make much sense - the datepart of month  will always be in the range of 1 to 12, so that whole part is superfluous.
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35197491
@Qlemo: I have spend way to many hours after the screen. Sorry for the mixup! :-)
Somehow it won't change the value when I add an extra condition. :-S
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 35197521
Is Field_04 really varchar? You should always check with a control SELECT first, containing some identifying column, the columns you check for, and the expression you need to generate:

select field_04, date_part(mm, getdate()), case
      when  DATEPART(mm,getdate()) = 1 and Field_04<'3500' then '3500'
      when  DATEPART(mm,getdate()) = 2 and Field_04<'4500' then '4500'
      else field_04
       end as expr
from items where Condition = 'A'

Further you should always provide an else - else the result is NULL if no condition is met.
0
 

Author Comment

by:Clyde_Radcliffe
ID: 35198393
@Qlemo: Thanks! I didn't realize that I should use the ELSE.
Everything works like a charm now!
0

Featured Post

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question