Apply functions 1

[robpotter]

I implemented several ApplySimple to Oracle in MSTR 7.1 with this structure in order to handle nulls and no Least function.

ApplySimple(&quot; CASE WHEN NVL(#2,0) < NVL(#1,0)
THEN NVL(#1,0)
ELSE Least(NVL(#2,0)-NVL(#1,0), NVL(#1,0))
END &quot;,
Sum(Fact0),Sum(Fact1), Sum(Fact2))

-> One question is whether in 7.2 (7i) I can simplify it to the following due to native Case and Least statements:

CASE(WHEN Sum(Fact2) < Sum(Fact1)
THEN Sum(Fact1) ELSE Least(Sum(Fact2)- Sum(Fact1),Sum(Fact0)END)

or, alternatively:

CASE(WHEN Metric2 < Sum(Metric1)
THEN Sum(Metric1) ELSE Least(Metric2-Metric1,Metric1))

where the metrics corresponding to the facts

-> Another question is which version of MSTR first offered the CASE and LEAST/GREATEST functions? Was it 7.2?

Thanks.

 

[nlim]

I think you meant to type

CASE(WHEN Metric2 < Metric1
THEN Sum(Metric1) ELSE Least(Metric2-Metric1,Metric1))

this is the best option as you can leverage the reuasability of metric 1 and 2. if you change these metrics, your formula will still work without having to do any edits to the formula.

i'm not sure when the case least greatest function came out...

 

[robpotter]

Thanks nlim,

Your reply was helpful. Since I am using 7.0 I couldn't use several of the nice enhancements in 7.2 (aka 7i).

Actually, I have to thank you for your earlier posting from May 2002 (See thread395-278718) where you replied about calculating min of two values. I tested using internal least function on metics in 7.0 and have to resort use applysimple to take advantage of DB's least function instead.

Also, I have to move null handling to RDBMS via the function- NVL (Oracle), IsNull (SQL Server), and Coalesce (DB2) because conditions and calcs done in the database don't benefit from MSTR's auto handling of nulls.

Robert