US and Worldwide: +1 (866) 660-7555
Results 1 to 3 of 3

Thread: Mondrian Error:WHERE clause expression returned set with more than one element.

  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Default Mondrian Error:WHERE clause expression returned set with more than one element.

    Hi,

    I am making small modifications on a project and If someone can give me a kindly help I will be really thankful.

    Let me give a brief introduction of the scenario, I have database with one table only where there are some MDX queries with Mondrian, the table has some fields so I will resume them to simplify.

    Let's imagine the table is composed by:

    Code:
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `coverage` varchar(8) NOT NULL,
      `spot` smallint(4) NOT NULL,
      `date_time` datetime NOT NULL,
      `year` smallint(6) NOT NULL,
      `month` tinyint(4) NOT NULL,
      `month_name` char(3) NOT NULL,
      `day` tinyint(4) NOT NULL,
      `hour` tinyint(4) NOT NULL,
      `hour_part` tinyint(4) NOT NULL,
      `minute` tinyint(4) NOT NULL,
      `status` varchar(8) NOT NULL
      `identifier` varchar(16) NOT NULL,
      `identifier_2` varchar(16) DEFAULT NULL,
    I have this schema:

    Code:
        <Dimension name="Spot">
          <Hierarchy hasAll="true" allMemberName="All">
            <Level name="Coverage" column="coverage"/>
            <Level name="Spot" column="spot"/>
          </Hierarchy>
        </Dimension>
    
     <!-- Year > Month > Day > Hour -->      
        <Dimension name="Time">
          <Hierarchy hasAll="true" allMemberName="All">
            <Level name="Year" column="year" type="Numeric" uniqueMembers="true"/>
            <Level name="Month" column="month" ordinalColumn="month" nameColumn="month_name"/>
            <Level name="Day" column="day" type="Numeric" formatString="0#"/>
            <Level name="Hour" column="hour" type="Numeric" formatString="0#"/>
            <Level name="HourPart" column="hour_part" type="Numeric" formatString="0#"/>
            <Level name="Minute" column="minute" type="Numeric" formatString="0#"/>
          </Hierarchy>
        </Dimension>
    
    <!-- Status -->      
        <Dimension name="Status">
          <Hierarchy hasAll="true" allMemberName="All">
            <Level name="Status" column="status" uniqueMembers="true"/>
          </Hierarchy>
        </Dimension>
    
        <!-- IMSI -->      
        <Dimension name="IDENTIFIER">
          <Hierarchy hasAll="true" allMemberName="All">
            <Level name="IDENTIFIER" column="identifier" uniqueMembers="true"/>
          </Hierarchy>
        </Dimension>
    
    <!-- ImsiOnly -->      
        <Dimension name="IDENTIFIERONLY">
          <Hierarchy hasAll="true" allMemberName="All">
            <Level name="IDENTIFIERONLY" column="identifier_2" uniqueMembers="true"/>
          </Hierarchy>
        </Dimension>
    
    etc...
    And is working fine, for instance if I execute the following query there is no problem at all:

    Code:
    WITH MEMBER Spot.M1 AS AGGREGATE({Spot.Coverage.Global,Spot.Coverage.Regional,Spot.Coverage.Narrow}) SELECT {Status.[OK],Status.[NotOK]} ON ROWS ,{Time.[2013].[May].[30],Time.[2013].[May].[31],Time.[2013].[Jun].[1],Time.[2013].[Jun].[2],Time.[2013].[Jun].[3],Time.[2013].[Jun].[4],Time.[2013].[Jun].[5],Time.[2013].[Jun].[6]} ON COLUMNS FROM Registrations WHERE (IDENTIFIER.[90],Measures.Total,Spot.M1)
    Now I have a new scenario where I have to do a query with an "OR" between identifier and identifier_2, If i execute the following query:

    Code:
    WITH MEMBER Spot.M1 AS AGGREGATE({Spot.Coverage.Global,Spot.Coverage.Regional,Spot.Coverage.Narrow}) SELECT {Status.[OK],Status.[NotOK]} ON ROWS ,{Time.[2013].[May].[30],Time.[2013].[May].[31],Time.[2013].[Jun].[1],Time.[2013].[Jun].[2],Time.[2013].[Jun].[3],Time.[2013].[Jun].[4],Time.[2013].[Jun].[5],Time.[2013].[Jun].[6]} ON COLUMNS FROM Registrations WHERE ({(IDENTIFIER.[90],IDENTIFIER2,Measures.Total,Spot.M1), (IDENTIFIER,IDENTIFIER2.[90],Measures.Total,Spot.M1)})
    It works well, except if there is a row with IDENTIFIER=IDENTIFIER_2, in this case, I got the following error:

    Mondrian Error:WHERE clause expression returned set with more than one element.


    I am a newbie with Mondrian, so I will really thankful if someone can help me.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2013
    Posts
    4

    Default

    Hi,

    I've upgraded the version of mondrian and now the error is gone, however, instead of the error now when there is a case where at least one row has IDENTIFIER=IDENTIFIER_2 , the "number"of Status.[Ok] and Status.[NotOk] are not filtered (it counts all the existing rows) and it doubles the number.

    Someone can help me ?

  3. #3
    Join Date
    Mar 2013
    Posts
    4

    Default

    Hi, I guess I solved this problem, however, I have to confess, the methodology was "trial and error" and I do not understand why is solved, but if someone feels comfortable to explain I will appreciate. The solution was to modify the query for:

    Code:
    WITH MEMBER Spot.M1 AS AGGREGATE({Spot.Coverage.Global,Spot.Coverage.Regional,Spot.Coverage.Narrow}) SELECT {Status.[OK],Status.[NotOK]} ON ROWS ,{Time.[2013].[May].[30],Time.[2013].[May].[31],Time.[2013].[Jun].[1],Time.[2013].[Jun].[2],Time.[2013].[Jun].[3],Time.[2013].[Jun].[4],Time.[2013].[Jun].[5],Time.[2013].[Jun].[6]} ON COLUMNS FROM Registrations WHERE ({(IDENTIFIER.[90],IDENTIFIER2,Measures.Total,Spot.M1), (IDENTIFIER,IDENTIFIER2.[90],Measures.Total,Spot.M1), (IDENTIFIER.[90],IDENTIFIER2.[90],Measures.Total,Spot.M1)})

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •