SELECT * in MSTR?

[JRO061601]

Has anyone tried to generate a query in MSTR that does a SELECT *? I can only get so far, creating an attribute defined as - ApplySimple("*"). But when I put it in a query, MSTR thinks it's a column and aliases it as 'CustCol'.

And the bigger question, even if we could generate the syntax wanted, would the MSTR engine be able to handle the result set?

 

[nlim]

what is the requirement here? Are you displaying the result, or exporting it to excel, or a datamart table?

 

[JRO061601]

I'm trying to work around a proprietary database quirk with the RANK function. If I have a query like this:

SELECT COL_ID, RANK(FACT)
FROM TABLE
GROUP BY COL_ID

it fails. I need to build a query like this:

SELECT *, RANK(FACT)
FROM (
SELECT COL_ID, FACT
FROM TABLE
GROUP BY COL_ID)

The goal is to display the result set in MSTR. I'm guessing the xtab engine wouldn't handle it though.

 

[entaroadun]

I don't think this can work. Each column in the result set has to be attached to an object, whether it be a specific attribute form or metric. MSTR won't create these things dynamically from a *; it has to know explicitly what you want it to do.

I guess the real question is what results you want to get; and what the quirks of the Rank function on your platform are. If you can write those up, we can hammer at a solution.

 

[chael]

[I'm helping JRO on this problem so I'll respond]

I think the answer to this question is "wait 'til 8" -- the free form SQL would resolve this in theory -- but unfortunately that is not an adequate answer right now. We know that doing this type of query isn't natural for MicroStrategy, so I wonder if there is a way to do this using an apply statement.

The crux of the issue is that the RDBMS we are using has a rank function, but the typical syntax we'd like to use isn't available yet. We can't use the following form because of a limitation with group by / aggregation statements involving the native ODBC rank:

[highlight]
select a,b, rank() c
from table
group by a,b
[/highlight]

...so instead valid syntax is:

[highlight]
select *, rank() c
from table
[/highlight]

We also can't force the select * into a subquery.

The solution to this problem is to build the syntax into a view and call it as a column...but we're looking to see if there are any other creative approaches out there that we have not considered.

Thanks!

 

[nlim]

does

SELECT RANK(FACT), col_id
FROM (
SELECT COL_ID, FACT
FROM TABLE
GROUP BY COL_ID)

work on your db?

 

[JRO061601]

nlim,

how would you get the sql engine to drop the 'group by col_id' clause in the outer query in your example?

thanks...

 

[entaroadun]

Weird RANK syntax. It doesn't make any sense...

Anyways, you can do the RANK on the App Server. Just set the VLDB setting. That way, MSTR will issue a "select a,b,sum(c) from table group by a,b" statement, load the entire result set into memory, and perform the RANK there. Performance depends on your result set size.

The view's probably your best choice.