ORA-00979 error getting members of a dimension

[pablolmedorado]

Hello everyone.

I'm getting an ORA-00979 error when I try to get the members of a dimension (http://localhost:5000/cube/support/members/centre):

DatabaseError: (cx_Oracle.DatabaseError) ORA-00979: not a GROUP BY expression [SQL: 'SELECT sp_centre.name AS centre \nFROM sp_support JOIN sp_centre ON sp_support.centre = sp_centre.id GROUP BY centre']

I've simplified my model.json the maximum to make it easy to understand:

{
    "cubes": [
        {
            "name": "support",
            "dimensions": [
                "centre"
            ],
            "joins": [
                {"master":"centre", "detail":"sp_centre.id"}
            ],
            "mappings": {
                "centre": "sp_centre.name"
            }
        }
    ],
    "dimensions": [
        {
            "name": "centre"
        }
    ]
}

My slicer.ini

[workspace]
log_level: info

[server]
host: localhost
port: 5000
reload: yes
prettyprint: yes
allow_cors_origin = *
json_record_limit = 5000

[store]
type: sql
url: oracle://fakeSchema:passwd@fakeHost:1591/DOAM48

[models]
main: model.json

If I use SQLite instead of Oracle, I have no problems :'(

I read that Oracle does not allow "Group by" with aliases, but I don't know what should I do to avoid this error.

Thanks in advance. You have done a great job =)

PS: I'm using the latest version of cubes (1.1)

[pablolmedorado]

If I perform an aggregate with "drilldown=centre" the query that appears in the log is:

SELECT sp_centre.name AS centre, count(DISTINCT sp_support.supportid) AS number_support_elements FROM sp_support JOIN sp_centre ON sp_support.centre = sp_centre.id GROUP BY sp_centre.name

and it runs ok.

I also tried to run the query that "members" function runs without the alias

SELECT sp_centre.name FROM sp_support JOIN sp_centre ON sp_support.centre = sp_centre.id GROUP BY sp_centre.name

and it also runs ok. The only problem is the use of an alias with "group by".

[pablolmedorado]

I think I have found the exact line that causes the error:

cubes/cubes/sql/browser.py

https://github.com/DataBrewery/cubes/blob/master/cubes/sql/browser.py#L303

statement = statement.group_by(*statement.columns)

If I write by hand the name of the column instead of *statement.columns, the members method works well. How could I get the physical name of a column given its alias?

Thanks again! =)

[pablolmedorado]

Finally I solved the problem changing this line: statement = statement.group_by(*statement.columns) for this one: statement = statement.group_by(*statement._raw_columns)

I hope this helps others with the same problem.

[ghost]

@Stiivi @pablolmedorado can we close the issue?