We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Case statement problem
Posted on 2011-09-28
I have this :
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
END
)
but I need to add another condition :
Instead of :
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
I need this :
If (OldPrice - ProductCost) < 5.00
THEN 5.00 - (OldPrice - ProductCost)
ELSE OldPrice << need to add that
I need to apply this to the other "WHEN" conditions too.
How do I code this? thanks
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
END
)
but I need to add another condition :
Instead of :
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
I need this :
If (OldPrice - ProductCost) < 5.00
THEN 5.00 - (OldPrice - ProductCost)
ELSE OldPrice << need to add that
I need to apply this to the other "WHEN" conditions too.
How do I code this? thanks
[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
4-
3
2- +2
11 Comments
Is this what you mean?
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
Not 100% sure what you're trying to get. Is this what you're looking for?
CASE
WHEN OldPrice < 5 THEN CASE WHEN (OldPrice = ProductCost) < 5.00 THEN 5.00 - (OldPrice - ProductCost) ELSE OldPrice END
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
END
CASE
WHEN OldPrice < 5 THEN CASE WHEN (OldPrice = ProductCost) < 5.00 THEN 5.00 - (OldPrice - ProductCost) ELSE OldPrice END
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
END
The case has only one ELSE condition , but I believe that what you want is the following to return the OldPrice for the case when none of the conditions are meet:
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
Perhaps you want this?
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN (OldPrice - ProductCost) < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN (OldPrice - ProductCost) BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN (OldPrice - ProductCost) BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
Otherwise it seems a straightforward conversion of what you already have...
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
If ((OldPrice - ProductCost) < 5)
THEN
5 - (OldPrice - ProductCost)
ELSE
OldPrice
If (BETWEEN 5 and 10)
THEN
8 - (OldPrice - ProductCost)
ELSE
OldPrice
If (BETWEEN 10 and 20)
THEN
12 - (OldPrice - ProductCost)
ELSE
OldPrice
END
)
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN (OldPrice - ProductCost) < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN (OldPrice - ProductCost) BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN (OldPrice - ProductCost) BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
Otherwise it seems a straightforward conversion of what you already have...
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
If ((OldPrice - ProductCost) < 5)
THEN
5 - (OldPrice - ProductCost)
ELSE
OldPrice
If (BETWEEN 5 and 10)
THEN
8 - (OldPrice - ProductCost)
ELSE
OldPrice
If (BETWEEN 10 and 20)
THEN
12 - (OldPrice - ProductCost)
ELSE
OldPrice
END
)
thanks all, . . I meant this :
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
NEED THIS INSTEAD :
If (OldPrice - ProductCost) < 5.00
THEN 5.00 - (OldPrice - ProductCost)
ELSE OldPrice
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
NEED THIS INSTEAD :
If (OldPrice - ProductCost) < 8.00
THEN 8.00 - (OldPrice - ProductCost)
ELSE OldPrice
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
NEED THIS INSTEAD :
If (OldPrice - ProductCost) < 12.00
THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
Needed to change all the "WHEN"'s.
nap0leon, does your If statement do this?
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
NEED THIS INSTEAD :
If (OldPrice - ProductCost) < 5.00
THEN 5.00 - (OldPrice - ProductCost)
ELSE OldPrice
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
NEED THIS INSTEAD :
If (OldPrice - ProductCost) < 8.00
THEN 8.00 - (OldPrice - ProductCost)
ELSE OldPrice
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)
NEED THIS INSTEAD :
If (OldPrice - ProductCost) < 12.00
THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
Needed to change all the "WHEN"'s.
nap0leon, does your If statement do this?
CASE
WHEN (OldPrice - ProductCost) < 5.00 THEN 5.00 - (OldPrice - ProductCost)
WHEN (OldPrice - ProductCost) < 8.00 THEN 8.00 - (OldPrice - ProductCost)
WHEN (OldPrice - ProductCost) < 12.00 THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
WHEN (OldPrice - ProductCost) < 5.00 THEN 5.00 - (OldPrice - ProductCost)
WHEN (OldPrice - ProductCost) < 8.00 THEN 8.00 - (OldPrice - ProductCost)
WHEN (OldPrice - ProductCost) < 12.00 THEN 12.00 - (OldPrice - ProductCost)
ELSE OldPrice
END
Sorry - my IF statement above has several errors in it... I was distracted while writing it up and using ELSE in multiple IFs I don't think it will do what you really want anyway.
I think what you want is a variation of the first one (using WHENS and IFs) Gimme a minute to re-write it.
I think what you want is a variation of the first one (using WHENS and IFs) Gimme a minute to re-write it.
What you are asking for, I think, is the first of the two I posted which is the same as tim_cs's post:
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN ((OldPrice - ProductCost) < 5) THEN 5 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 8) THEN 8 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 12) THEN 12 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
But running it through some test data, I don't think it is doing what you really want for cases where marginal profit is > 12.
Consider the following:
Case for marginal profit < 5 (e.g., 2)
OldPrice = 20
ProductCost = 18
Price = 20 + 2 + (5-2) = 25
Case for marginal profit > 5 but less than 8 (e.g., 6)
OldPrice = 20
ProductCost = 14
Price = 20 + 2 + (8-6) = 24
Case for marginal profit > 8 but less than 12 (e.g., 10)
OldPrice = 20
ProductCost = 10
Price = 20 + 2 + (12-10) = 24
Case for marginal profit > 12 (e.g., 15)
OldPrice = 20
ProductCost = 5
Price = 20 + 2 + (15) = 37
Perhaps instead you want the Price to be simply the OldPrice with no modifications, in which case you would want this:
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN ((OldPrice - ProductCost) < 5) THEN OldPrice + 2.00 + 5 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 8) THEN OldPrice + 2.00 + 8 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 12) THEN OldPrice + 2.00 + 12 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN ((OldPrice - ProductCost) < 5) THEN 5 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 8) THEN 8 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 12) THEN 12 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
But running it through some test data, I don't think it is doing what you really want for cases where marginal profit is > 12.
Consider the following:
Case for marginal profit < 5 (e.g., 2)
OldPrice = 20
ProductCost = 18
Price = 20 + 2 + (5-2) = 25
Case for marginal profit > 5 but less than 8 (e.g., 6)
OldPrice = 20
ProductCost = 14
Price = 20 + 2 + (8-6) = 24
Case for marginal profit > 8 but less than 12 (e.g., 10)
OldPrice = 20
ProductCost = 10
Price = 20 + 2 + (12-10) = 24
Case for marginal profit > 12 (e.g., 15)
OldPrice = 20
ProductCost = 5
Price = 20 + 2 + (15) = 37
Perhaps instead you want the Price to be simply the OldPrice with no modifications, in which case you would want this:
UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.00 + (
CASE
WHEN ((OldPrice - ProductCost) < 5) THEN OldPrice + 2.00 + 5 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 8) THEN OldPrice + 2.00 + 8 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 12) THEN OldPrice + 2.00 + 12 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
ARGH... need an edit button - the last section should read:
Perhaps instead you want the Price to be simply the OldPrice with no modifications, in which case you would want this:
UPDATE Nop_ProductVariant
SET Price = (
CASE
WHEN ((OldPrice - ProductCost) < 5) THEN OldPrice + 2.00 + 5 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 8) THEN OldPrice + 2.00 + 8 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 12) THEN OldPrice + 2.00 + 12 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
Perhaps instead you want the Price to be simply the OldPrice with no modifications, in which case you would want this:
UPDATE Nop_ProductVariant
SET Price = (
CASE
WHEN ((OldPrice - ProductCost) < 5) THEN OldPrice + 2.00 + 5 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 8) THEN OldPrice + 2.00 + 8 - (OldPrice - ProductCost)
WHEN ((OldPrice - ProductCost) < 12) THEN OldPrice + 2.00 + 12 - (OldPrice - ProductCost)
ELSE OldPrice
END
)
Featured Post
Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Exception Handling is in the core of any application that is able to dignify its name.
In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month13 days, 21 hours left to enroll
656 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.