[Mondrian] CASE/WHEN Statement in Where clause

Kleyson Rios kleyson.rios at saude.go.gov.br
Mon Jan 28 07:23:39 EST 2013


My dashboard has a combobox where the options are:
- All (static one)
- Desp. Medicas (come from a select)
- Honorarios (come from a select)

So, I need create filters based on the selection.

If my user select "All" my MDX should be:

select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY 
 Order({[Convenio].[Todos convenios].Children}, 
 [Measures].[Porcentagem], DESC) ON ROWS 
 from [Fatur] 
 where {[Tempo].[2011]}

But if my user choose "Honorarios" should be:

select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY 
 Order({[Convenio].[Todos convenios].Children}, 
 [Measures].[Porcentagem], DESC) ON ROWS 
 from [Fatur] 
 where Crossjoin({[Tempo].[2011]}, {[Tipo Despesa].[Honorario]}) 


The same idea in SQL would be:

select fields
from table
where case when ${type} = 'All' then 1=1
           else type = 'Honorarios'
      end



I've read a lot trying to find the answer but nothing until now.

Thanks for the help.

Regards.



-- 
Kleyson Rios
Gestor de TI
Coordenador de BI / Banco de Dados
Gerência de Tecnologia da Informação
Secretaria de Estado da Saúde de Goiás
(62) 3201-3707

Se não pode vencer pelo talento, vença pelo esforço. (Dave Weinbaum) 


----- Original Message -----
From: "Matt Campbell" <mcampbell at pentaho.com>
To: "Mondrian developer mailing list" <mondrian at pentaho.org>
Sent: Friday, January 25, 2013 5:52:52 PM
Subject: Re: [Mondrian] CASE/WHEN Statement in Where clause





Kleyson's example didn't actually have a Boolean in the WHERE, it had a CASE statement that evaluates conditionally to a set. That's supported, although a little odd. The following query will run fine in Mondrian: 



select from sales WHERE 

case when 'All' = 'All' then gender.f else gender.m end 



Kleyson, can you give a little background on the intent of your query? There are probably more straightforward ways to construct it. 





-----Original Message----- 
From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Julian Hyde 
Sent: Friday, January 25, 2013 2:31 PM 
To: Mondrian developer mailing list 
Subject: Re: [Mondrian] CASE/WHEN Statement in Where clause 



You've read the tutorial and you still think you can put a boolean expression in the WHERE clause? Read it again. 



Julian 





On Jan 25, 2013, at 10:30 AM, Kleyson Rios < kleyson.rios at saude.go.gov.br > wrote: 



> I Julian, 

> 

> Thanks for the answer. 

> 

> I've already read the tutorial. 

> 

> I am asking because in Microsoft website I saw an example using CASE/WHEN for select. 

> 

> I need to use the same idea for the WHERE. 

> 

> Can you give me a way in how to do that ? 

> 

> Thanks in advance. 

> 

> Kleyson Rios. 

> 

> ----- Mensagem original ----- 

> De: "Julian Hyde" < jhyde at pentaho.com > 

> Para: "Mondrian developer mailing list" < mondrian at pentaho.org > 

> Enviadas: Sexta-feira, 25 de Janeiro de 2013 16:14:45 

> Assunto: Re: [Mondrian] CASE/WHEN Statement in Where clause 

> 

> You are making a mistake that many people make when learning MDX. You are assuming that the MDX WHERE clause has the same purpose as the SQL WHERE clause. MDX WHERE clause does not contain a boolean expression. I suggest that you read a tutorial on MDX. 



> 

> Julian 

> 

> 

> On Jan 25, 2013, at 10:12 AM, Kleyson Rios < kleyson.rios at saude.go.gov.br > wrote: 

> 

>> 

>> Hi, 

>> 

>> Is it possible use CASE/WHEN in Where clause ? 

>> 

>> For example: 

>> 

>> select NON EMPTY {[Measures].[ConstStr]} ON COLUMNS, NON EMPTY 

>> Order({[Convenio].[Todos convenios].Children}, 

>> [Measures].[Porcentagem], DESC) ON ROWS from [Fatur] where CASE WHEN 

>> 'All' = 'All' then {[Tempo].[2011]} 

>> ELSE Crossjoin({[Tempo].[2011]}, {[Tipo Despesa].[Honorario]}) 

>> END 

>> 

>> Thanks in advance. 

>> 

>> Kleyson Rios. 

>> _______________________________________________ 

>> Mondrian mailing list 

>> Mondrian at pentaho.org 

>> http://lists.pentaho.org/mailman/listinfo/mondrian 

> 

> _______________________________________________ 

> Mondrian mailing list 

> Mondrian at pentaho.org 

> http://lists.pentaho.org/mailman/listinfo/mondrian 

> _______________________________________________ 

> Mondrian mailing list 

> Mondrian at pentaho.org 

> http://lists.pentaho.org/mailman/listinfo/mondrian 



_______________________________________________ 

Mondrian mailing list 

Mondrian at pentaho.org 

http://lists.pentaho.org/mailman/listinfo/mondrian 
_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian


More information about the Mondrian mailing list