table ordering of MSTR's FROM clause 1

[138006]

hi all,
What is the logic followed by MSTR in generating a SQL specifically the From clause order.

Say I have 5 attributes from 5 tables. How will MSTR order these tables in the Where Clause? It seems it doesn't do so based on the order the attributes are chosen.

Is it possible to make the alias static for a table? i.e say i have a table Time. Can I make it mandatory that Time will always be the alias a11 in all MSTR reports??

There is an option in VLDB but that merely changes the order but keeps the alias same for that report SQL
Thanks

 

[138006]

I understand ur point logically. But a select can come in the Where part. How can it come in the SELECT part of a SQL?

 

[entaroadun]

Just try it! It will work! Trust me!

 

[138006]

Thanks for the suggestion .
Do note that I have a metric in the report as well.
So the Sql becomes

Select
dim1,
this decode,
sum(fact)
from ....
where ...
group by dim1, this decode.

Now Oracle gives an error while trying to do a GROUP BY on this decode attribute.
How to solve it. Cos I will have metrics in all reports and all will require a GROUP BY on this deocde

Thanks

 

[entaroadun]

Ah. GROUP BY on a DECODE. You're stuck.

Can you find some way to write this SQL statement so it returns properly? If you can write that SQL, then we can try to make MSTR generate it.

Don't know if this will work, but if C1, C2, and C3 are all numbers, then:

ApplySimple(
  "(
   (((SELECT x.C2 from T2 x where x.key2 = #1) = -1) *
   -1 * (SELECT y.C1 from T1 y where y.key1 = #0)) +
   (((SELECT x.C2 from T2 x where x.key2 = #1) <> -1) *
   -1 * (SELECT z.C3 from T2 z where z.key2 = #1))
   )",
  Fact.key1,
  Fact.key2)

The key to this is if C2 = -1, then the boolean compare will return a TRUE, which Oracle should implicitly convert to -1. -1 * -1 * C1 = C1. The second compare is C2 <> -1, which should return a FALSE, which Oracle should implicitly convert to 0. 0 * -1 * C3 = 0 Then you add the two, which should return C1.

To review:
If C2 = -1: (-1 * -1 * C1) + ( 0 * -1 * C3) = C1
If C2 <> -1: ( 0 * -1 * C1) + (-1 * -1 * C3) = C3

That's a potential way to do the logic you need without DECODE or CASE. You may have to mess around with CAST or some other data type conversion function to get the boolean to convert to a number.

 

[138006]

Thanks,
But in our case these are all texts.

The IF part is number but THEN and ELSE are all texts.

Any help?

 

[entaroadun]

Try embedding the CASE logic in a view. If that doesn't work, then it will have to be materialized. Can you think of a way with NULL logic?