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
9
Medium Priority
?
354 Views
Last Modified: 2013-01-15
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)

Open in new window


to a SQL CASE statement?

The name of the fields are the same.

Thank you!
0
Comment
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
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 13

Expert Comment

by:LIONKING
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

by:awking00
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

by:metropia
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 800 total points
ID: 38768967
If I'm reading your formula correctly, you're looking for this:

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)

Open in new window


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

by:
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)

Open in new window

0
 
LVL 28

Expert Comment

by:Ryan McCauley
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

by:metropia
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

by:LIONKING
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

by:Ryan McCauley
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

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

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…

715 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