Group on measures not working

[Former Member]

Hi, I want to find out the most common values for field "x", and "x" is a measure with DECIMAL type. So I ran the 1st sql, The output has only 1 record with some random value for "x". I know "x" has many different values. If "x" is NVARCHAR, it works well. But when "x" is DECIMAL, it doesn't work. I have to run the 2nd sql in order to get the results. But I'm concerned about performance issues of the 2nd sql, does it use too much computing resources? Can someone help? Thanks.

SELECT "x", count(*) as cnt

FROM "table1"

GROUP BY "x"

ORDER BY cnt DESC

SELECT "x", count(*) as cnt

FROM (SELECT "x" FROM "table1")

GROUP BY "x"

ORDER BY cnt DESC

[pfefferf]

Hi,

I'm wondering how you can get different result for both statements, because in fact internally they are executed the same way as the explain plan shows.

On my environment (SPS10) system both statements delivers the correct result.

On which SPS level you are? Can you share the statements with results and the explain plan?

Regards,

Florian

[Former Member]

I use SAS and ODBC connection to pass through the SQL script to HANA.

[pfefferf]

Then I would recommend to trace your statements to check how the statement written in SAS is actually executed on HANA. In the form like described above there is no issue on HANA side.