-
Bug
-
Resolution: Unresolved
-
Major
-
None
-
7.7.0.Final
-
NEW
-
NEW
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
