Uploaded image for project: 'jBPM'
  1. jBPM
  2. JBPM-7309

COUNT in select statement for dataset not correctly parsed

    XMLWordPrintable

Details

    • NEW
    • NEW

    Description

      The following select statement does not work correctly when used in a dataset:

      select S.state_name, COUNT( I.incident_reason ) from FACT_INCIDENTS as I INNER JOIN DIM_STATES as S ON I.state_id = S.state_id GROUP BY S.state_name
      

      This statement gets parsed and the actual statement created by dashbuilder this:

      SELECT COUNT(*) FROM (SELECT state_name, COUNT( I.incident_reason ) FROM (select S.state_name, COUNT( I.incident_reason ) from FACT_INCIDENTS as I INNER JOIN DIM_STATES as S ON I.state_id = S.state_id GROUP BY S.state_name) AS `dbSQL`) AS `dbSQL`
      

      The problem is the COUNT. That field should be provided an alias, and that alias should be used in the wrapper dahsbuilder query. The error you'll get is:

      Unknown column 'I.incident_reason' in 'field list'
      

      A workaround is to define the alias yourself, like so:

      select S.state_name, COUNT( I.incident_reason ) as number_of_incidents from FACT_INCIDENTS as I INNER JOIN DIM_STATES as S ON I.state_id = S.state_id GROUP BY S.state_name
      

      Attachments

        Activity

          People

            david.magallanes David Gutierrez
            rhn-gps-ddoyle Duncan Doyle
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: