Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# convert inline IF to CASE statement

Posted on 2013-01-11
Medium Priority
354 Views
can someone help me convert the following formula:

``````IIf(CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", Fields!OrderBalanceQty.Value, CDec(0)))) <> 0, (CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", (Fields!OrderBalanceQty.Value * Fields!OrderPrice.Value), CDec(0)))) / CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", Fields!OrderBalanceQty.Value, CDec(0))))), 0)
``````

to a SQL CASE statement?

The name of the fields are the same.

Thank you!
0
Question by:metropia
[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
• 3
• 2
• 2
• +2

LVL 13

Expert Comment

ID: 38768474
I could help you, but I kind of don't understand the formula.
I see you have a sum so I guess you're grouping somehow, but I see that you have orderBalanceQuantity * OrderPrice / OrderBalanceQuantity

Wouldn't that give you OrderPrice?
Sorry if this is a dumb question, I just want to understand what you need.
0

LVL 32

Expert Comment

ID: 38768510
Can you describe what you're trying to accomplish in plain language? Rather than trying to convert, it is often easier to just write the syntax if we know the objective.
0

Author Comment

ID: 38768516
the formula was part of a report, i want to calculate the weighted price. is not dumb question, i am also confused by some of the formulas i am finding in some of the reports, and no one to ask.
0

LVL 28

Assisted Solution

Ryan McCauley earned 800 total points
ID: 38768967

``````SUM(CASE WHEN Fields!OrderLineStatus.Value <> "OPEN" THEN 0
WHEN Fields!OrderBalanceQty.Value = 0 THEN 0
ELSE Fields!OrderBalanceQty.Value *
Fields!OrderPrice.Value /
Fields!OrderBalanceQty.Value
END)
``````

Effectively, you're returning zero if either the LineStatus <> "OPEN" or if the BalanceQuantity=0. Assuming both of those are false (status = OPEN and Quantity <> 0), it performs the calculation.
0

LVL 27

Accepted Solution

Chris Luttrell earned 1000 total points
ID: 38769110
He is taking an IF statement from SSRS and trying to accomplish the same outcome in a SQL statement.  Given the Fields names are the same as the Columns on the table this code should work in SQL:
``````SUM(CASE WHEN OrderLineStatus <> "OPEN" THEN 0
WHEN OrderBalanceQty = 0 THEN 0
ELSE OrderBalanceQty *
OrderPrice /
OrderBalanceQty
END)
``````
0

LVL 28

Expert Comment

ID: 38770017
I figured, but I wasn't sure if the field names actually matched, so I thought I'd let the OP swap those out themself - if they match exactly, then yours is what they'd actually use.
0

Author Comment

ID: 38775012
question,

what would be the operator order on this part?

OrderBalanceQty *  OrderPrice / OrderBalanceQty

should it he like:

( OrderBalanceQty *  (OrderPrice / OrderBalanceQty ))

or

((OrderBalanceQty *  OrderPrice) / OrderBalanceQty )
0

LVL 13

Assisted Solution

LIONKING earned 200 total points
ID: 38775055
The order of multiplication and division operators is the same... It doesn't matter in what order you put them, the result will not vary.

For example:

OrderBalanceQty *  OrderPrice / OrderBalanceQty = ( OrderBalanceQty *  (OrderPrice / OrderBalanceQty )) = ((OrderBalanceQty *  OrderPrice) / OrderBalanceQty ) = OrderPrice

The only way the value would change is if the operation (multiplication) is done in the denominator, which doesn't seem likely in this case.
0

LVL 28

Assisted Solution

Ryan McCauley earned 800 total points
ID: 38775228
It would execute left to right (since order of operations on multiplication/division don't matter), but that won't affect the results at all. If you had addition in there, it would execute the multiplication first, using the standard order of operations.

The only thing you need to be concerned about for your calculation is the divisor - it needs to be non-zero or you'll get the error. As long as it's non-zero, the other values can be anything.
0

## Featured Post

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
###### Suggested Courses
Course of the Month7 days, 9 hours left to enroll