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

COUNT in select statement for dataset not correctly parsed

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 7.7.0.Final
    • Fix Version/s: None
    • Component/s: Dashboard Builder
    • Labels:
      None
    • Environment:

      Red Hat PAM 7.0.0.CR2

    • Docs QE Status:
      NEW
    • QE Status:
      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
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                davidredhat David Gutierrez
                Reporter:
                McCloud Duncan Doyle
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: