Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: aggregate functions fail on calculated fields

  1. #1
    Join Date
    Jun 2013
    Posts
    6

    Default aggregate functions fail on calculated fields

    hi pentaho users, i am new to pentaho bi solution and i came upon a problem when using the query editor and querying aggregated calculated fields

    i am using pentaho metadata editor's latest stable version 4.8.0

    here are the sql and mql codes generated by pentaho for this setup
    Name:  aggregates.jpg
Views: 77
Size:  10.1 KB
    Code:
    SELECT 
              BT_DIMSITES_DIMSITES.designation AS COL0
             ,((nombre_present / nombre_total) * 100) AS COL1
    FROM 
              dimsites BT_DIMSITES_DIMSITES
             ,factpresence BT_FACTPRESENCE_FACTPRESENCE
    WHERE 
              ( BT_DIMSITES_DIMSITES.cle_site = BT_FACTPRESENCE_FACTPRESENCE.cle_site_p )
    GROUP BY 
              BT_DIMSITES_DIMSITES.designation
    Code:
    <mql>
      <domain_type>relational</domain_type>
      <domain_id>sodexodwbi</domain_id>
      <model_id>Presence</model_id>
      <model_name>Statistique presence</model_name>
      <options>
        <disable_distinct>false</disable_distinct>
        <limit>-1</limit>
      </options>
      <selections>
        <selection>
          <view>Sites</view>
          <column>BC_DIMSITES_DESIGNATION</column>
          <aggregation>none</aggregation>
        </selection>
        <selection>
          <view>fait_presence</view>
          <column>BC_FACTPRESENCE_RAPPORT</column>
          <aggregation>average</aggregation>
        </selection>
      </selections>
      <constraints/>
      <orders/>
    </mql>
    on the generated sql code, there is no aggregate function applied to the calculated field

    ((nombre_present / nombre_total) * 100)
    if its not a calculated field the aggregate function are well applied.

    "is the formula exact" is on true because if i dont i get this error

    Code:
    An error occurred executing SQL: 
    SELECT 
              BT_DIMSITES_DIMSITES.designation AS COL0
             ,AVG(BT_FACTPRESENCE_FACTPRESENCE.`((nombre_present / nombre_total) * 100)`) AS COL1
    FROM 
              dimsites BT_DIMSITES_DIMSITES
             ,factpresence BT_FACTPRESENCE_FACTPRESENCE
    WHERE 
              ( BT_DIMSITES_DIMSITES.cle_site = BT_FACTPRESENCE_FACTPRESENCE.cle_site_p )
    GROUP BY 
              BT_DIMSITES_DIMSITES.designation
    
    
    Unknown column 'BT_FACTPRESENCE_FACTPRESENCE.((nombre_present / nombre_total) * 100)' in 'field list'
    what am i doing wrong here, thanks for your help

  2. #2
    Join Date
    Jun 2013
    Posts
    6

    Default

    i figured out what went wrong, when defining formulas, i was supposed to wrap field id's in brakets
    so this
    ((nombre_present / nombre_total) * 100)
    become this
    (([nombre_present] / [nombre_total]) * 100)

  3. #3
    Join Date
    Jun 2013
    Posts
    44

    Default

    i've tried my best to solve it but do you know what actaully happened to me is a more confusion the more i have gone through the read .... now moving over the web if thers is any apt help come to my way .. also discussing with my mentor to have an efficient clue about the matter you got stuck with..

  4. #4
    Join Date
    Jun 2013
    Posts
    44

    Default

    waiting for some apt as well as I'm also troubling with the same

Tags for this Thread

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.