Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: MDX Query doubt - WHERE clause expression returned set with more than one element

  1. #1

    Question MDX Query doubt - WHERE clause expression returned set with more than one element

    Hello everydoby!

    I'm a newbie in MDX and I'm currently struggling here to make a (supposed to be) simple query works.

    The query is as follows:

    select {[Measures].[Nb Appels],[Measures].[Nb Appels factures],
    [Measures].[Nb Appels non-factures],
    [Measures].[Duree Heure],
    [Measures].[Duree Heure vraiment facturee],
    [Measures].[Duree Heure non-facturee],
    [Measures].[Duree Moyenne]} on columns,
    {{NumCouleurSet}} on rows
    from [Appels]
    where
    [Temps].[{startYear}].[{startMonth}].[{startDay}]:[Temps].[{endYear}].[{endMonth}].[{endDay}]

    {NumCouleurSet}, {startYear}, {startMonth} etc are all parameters that will be replaced in runtime by the correct values.

    Currently the query returns the following error:
    WHERE clause expression returned set with more than one element

    Okay, sets in the where clause can't have more than one element... but then, how can I proceed? I need to show all the above Measures, for each number in the set for the rows axis, and, due to a demand from the Pivot viewer, my result set must have two dimensions - so I cannot simply move my where clause to a third axis.

    Does anyone know what should I do?

    Thanks in advance for any help.

  2. #2

    Default

    Hm, I'm not an expert in mdx, but this is what I think:

    In the where-clause, every dimension can appear only one time! This is damn ugly and I spend a lot of time for dirty workarounds...
    What you want is to select multiple sub-cubes and to glue them together to a smaller cube. I found a workaround for me, but I still have problems with it.
    I defined a new member where the wanted sub-cubes are aggregated. This member is used in the where-clause.

    Code:
    with
    member [h].[selection] as   'Aggregate( {[h].[all].[1],   [h].[all].[3]} )'
    
    select
    {[Measures].[max_t_f], [Measures].[avg_t_f]}
    ON COLUMNS,
    {[chron].[whole time].[2007]})
    ON ROWS
    
    from [cubeName]
    where [h].[selection]
    Maybe it helps a bit....

  3. #3
    Join Date
    Nov 2007
    Posts
    12

    Default

    It looks like you are trying to select data from a date range. Here is something I've tried that seems to work okay.

    Code:
    with member [Posting_Date.posting_date].[AggDate] as
      'Aggregate(([Posting_Date.posting_date].[All Posting Dates].[{StartDate}] :
                  [Posting_Date.posting_date].[All Posting Dates].[{EndDate}]))'
    Good luck,
    Keith

  4. #4

    Default

    Thanks!
    The aggregation worked just fine!

  5. #5
    Join Date
    Nov 2007
    Posts
    1

    Unhappy Anyone can reply this question ??

    Quote Originally Posted by caveden View Post
    Hello everydoby!

    I'm a newbie in MDX and I'm currently struggling here to make a (supposed to be) simple query works.

    The query is as follows:

    select {[Measures].[Nb Appels],[Measures].[Nb Appels factures],
    [Measures].[Nb Appels non-factures],
    [Measures].[Duree Heure],
    [Measures].[Duree Heure vraiment facturee],
    [Measures].[Duree Heure non-facturee],
    [Measures].[Duree Moyenne]} on columns,
    {{NumCouleurSet}} on rows
    from [Appels]
    where
    [Temps].[{startYear}].[{startMonth}].[{startDay}]:[Temps].[{endYear}].[{endMonth}].[{endDay}]

    {NumCouleurSet}, {startYear}, {startMonth} etc are all parameters that will be replaced in runtime by the correct values.

    Currently the query returns the following error:
    WHERE clause expression returned set with more than one element

    Okay, sets in the where clause can't have more than one element... but then, how can I proceed? I need to show all the above Measures, for each number in the set for the rows axis, and, due to a demand from the Pivot viewer, my result set must have two dimensions - so I cannot simply move my where clause to a third axis.

    Does anyone know what should I do?

    Thanks in advance for any help.

    mondrian is very fine , but Demo program is complex , I need long time , only readable it's principle ,
    in addition , parameter, fileter mdx command has no Demo , I dont think it is a good idea.

  6. #6
    Join Date
    Jun 2008
    Posts
    12

    Default MDX Object not found in cube

    I'm attempting basically the same thing. I used the Aggregate solution suggested below, but I'm getting this error: "Mondrian Error:MDX object '[Time].[selection]' not found in cube 'Traffic'"

    Here is my query:
    Code:
    WITH MEMBER
    [Time].[selection] AS 'Aggregate( {[Time].[2008].[March].[24]:[Time].[2008].[March].[30]} )'
    SELECT
    {[Measures].[Views]} ON COLUMNS,
    {([Industry].Children)}  ON ROWS
    FROM [Traffic]
    WHERE [Time].[selection]
    What could cause this error?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.