Hi

I'd like to know how to apply structure

LIMIT dimension TO TOP (number of top member performers) BASEDON (measure)

in OLAP_TABLE.

For instance I have a cube SALES with 2 measures: SALES_VALUE and QUANTITY and a dimension PRODUCT with two levels: TOTAL and PRODUCT_NAME. Then I want to list 3 products with the highest SALES_VALUE and QUANTITY > 100.

I can create for example such query:

SELECT sales_val, quant, product_dsc
FROM TABLE(OLAP_TABLE(
'test_schema.test_aw DURATION SESSION',
null,
null,
'
MEASURE sales_val FROM sales_value
MEASURE quant FROM quantity

DIMENSION product WITH
HIERARCHY product_parentrel
INHIERARCHY product_inhier
ATTRIBUTE product_dsc FROM product_description

ROW2CELL r2c'))
WHERE
OLAP_CONDITION(R2C, 'LIMIT product TO product_levelrel eq ''PRODUCT_NAME''', 1)=1
AND OLAP_EXPRESSION_BOOL(R2C, ' quantity gt 100')=1
AND OLAP_CONDITION(R2C, 'LIMIT product TO TOP 3 BASEDON sales_value, 1)=1; ???????

Instruction above does not work completely well, because it will give 3 rows with top values of SALES_VALUE measure, but in this case OLAP_CONDITION(R2C, 'LIMIT product TO product_levelrel eq ''PRODUCT_NAME''', 1)=1 is not fulfilled (row with values of TOTAL_PRODUCT level appears).

One can create such query:

SELECT sales_val, quant, product_dsc
FROM TABLE(OLAP_TABLE(
'test_schema.test_aw DURATION SESSION',
null,
'LIMIT product TO TOP 3 BASEDON sales_value', ?????
'
MEASURE sales_val FROM sales_value
MEASURE quant FROM quantity

DIMENSION product WITH
HIERARCHY product_parentrel
INHIERARCHY product_inhier
ATTRIBUTE product_dsc FROM product_description

ROW2CELL r2c'))
where
OLAP_CONDITION(R2C, 'LIMIT product TO product_levelrel eq ''PRODUCT_NAME''', 1)=1
AND OLAP_EXPRESSION_BOOL(R2C, ' quantity gt 100')=1;

It leads to the same result as the first query.

What shuold I do to make OLAP_CONDITION(R2C, 'LIMIT product TO product_levelrel eq ''PRODUCT_NAME''', 1)
fulfill in query above?

I can add that query below works as it should (LIMIT TO TOP... omitted).

SELECT sales_val, quant, product_dsc
FROM TABLE(OLAP_TABLE(
'test_schema.test_aw DURATION SESSION',
null,
null,
'
MEASURE sales_val FROM sales_value
MEASURE quant FROM quantity

DIMENSION product WITH
HIERARCHY product_parentrel
INHIERARCHY product_inhier
ATTRIBUTE product_dsc FROM product_description

ROW2CELL r2c'))
WHERE
OLAP_CONDITION(R2C, 'LIMIT product TO product_levelrel eq ''PRODUCT_NAME''', 1)=1
AND OLAP_EXPRESSION_BOOL(R2C, ' quantity gt 100')=1;

Thank you in advance for any help

Peter

Hi

Thank you for your answer. It was really helpful. I had to modify it a bit to get it worked, because applying your way the condition about limiting to quantity qreater than 100 was omitted. I used:

OLAP_CONDITION(R2C, 'LIMIT product KEEP product_levelrel eq ''PRODUCT_NAME''', 1)=1
AND
OLAP_CONDITION(R2C, 'LIMIT product KEEP quantity gt 100',1)=1
AND
OLAP_CONDITION(R2C, 'LIMIT product KEEP TOP 3 BASEDON sales_value,1)=1
AND
OLAP_CONDITION(R2C, 'LIMIT product SORT TOP 3 BASEDON sales_value,1)=1

It does not omit condition about limiting to quantity qreater than 100 and returns 3 top performers, but still it is not sorted.

Regards

Peter

Hi

Thank you so much for your effort Shankar. Thanks to that I could really better understand how to use LIMIT command properly.

Regards

Peter