Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Problem in creating Metrics based on a Metrics

Problem in creating Metrics based on a Metrics

Problem in creating Metrics based on a Metrics

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?


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.


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...

entaroadun knows the answer to this...look at his thread:
Thread395-484865 luck since then entaroadun???

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).

To answer banda's question:

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

Thank you chael & entaroadun for your help.

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

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

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close