×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Problem in creating Metrics based on a Metrics

## Problem in creating Metrics based on a Metrics

(OP)
Hi All,
I have a report in which few metrics are calculated using other metrics in the report. These metrics are created using ApplySimpe and CASE statements.

The problem is the SQL generated is very complicated and it is bigger that 64kb.

Eg:  SELECT (des_amt * 20) as a
,(CASE WHEN (des_amt * 20) > 24
THEN des_des * 0.01
ELSE (des_amt * 20) ) as b
,(CASE WHEN (CASE WHEN (des_amt * 20) > 24
THEN des_des * 0.01
ELSE (des_amt * 20) ) > 50
THEN (des_amt * 20)
ELSE des_des * 0.01) as c
,......
goes on....

What I would like to see is
SELECT (des_amt * 20) as a
,(CASE WHEN a > 24
THEN des_des * 0.01
ELSE a ) as b
,(CASE WHEN b > 50
THEN a
ELSE b) as c
,.........

Can any one tell me which VLDB properties will help me to achieve this OR if there is any other way?

Thanks
-Sri

### RE: Problem in creating Metrics based on a Metrics

Sri - there is no VLDB setting that applies to what you are doing.

You should consider building a table for what you are trying to calculate and then model it in...should be pretty simple...

des_amt | des_amt_case

This will simply the SQL greatly.

Chael

### RE: Problem in creating Metrics based on a Metrics

One other suggestion...if you are on 7.2.x take a look at the BandingP function...

### RE: Problem in creating Metrics based on a Metrics

I haven't tried further with BandingP because of its inherent limitations (e.g. you can't choose between greater than and greater than or equal to when defining your range endpoints).

MSI 7.2 has several different Case functions.  Refer to the product documentation.

If this is not possible:

The SQL you want (the second set) is impossible.  You cannot alias a column and then have another column refer to that alias.

The best you can hope for is separate passes for each calc.  The first pass would get A.  The second pass would base itself off of the first pass and get B.  The third pass would get C...  It would actually be a pretty efficient query.

Nothing is that easy.  Try this approach.

Write your A metric with no ApplySimple.
Write three B metrics:
B part 1 would be des_des * 0.01 with a metric filter of A > 24.
B part 2 would be A with a metric filter of A <=24.
B would be B part 1 + B part 2.

Using this method, you should get one pass for A, three passes for B, three passes for C...  It's not pretty, but it's definitely better than the repeated Case statements.

Let us know if this works.

### RE: Problem in creating Metrics based on a Metrics

(OP)

I have few more questions entaroadun.

1) We have MS 7.2.1. Can you please tell me where or in which manual the CASE functionality is explained?

2) How can I do separate passes for each calc?

Thanks You
Sri

### RE: Problem in creating Metrics based on a Metrics

1)  Check the Analytical Functions reference.  I have 7.2.2 and am looking at page 204.  I believe 7.2.1 has the same book.

2)  Just create the compound metrics that you've been doing:
A = des_amt * 20
B = Case(A > 24, des_des * 0.01, A)
...

Check the SQL it generates.  Hopefully, it won't do the ugly nested CASE statements.

### RE: Problem in creating Metrics based on a Metrics

(OP)
1) No, the manuals for 7.2.1 & 7.2.2 are different. 7.2.1 does not have CASE functionality.

2) Even after building the Metrics as compound metrics, the SQL generated is same nested....

Any way, Thank you for your help.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!