Ambiguity in an SQL query when using custom queries through a SAP HANA data connection.

Ambiguity in an SQL query when using custom queries through a SAP HANA data connection.

Ambiguity in an SQL query when using custom queries through a SAP HANA data connection.

When using custom SQL one should be wary of column names that aren't 'regular' names. This can cause ambiguity in an SQL query. For example, if the alias name for a query is not supplied, the query would look like this: 

select sum("Sales") + 1 from "SomeTable" group by "Region"

The name of the resulting column will be sum("Sales") + 1.

This is expected. However, since this column will be 'used' in subsequent calls when running queries against HANA (or any database) there might be cases where that name contains characters that are not supported by the external system.

Spotfire currently does not detect this automatically. Hence the recommendation would be to always provide alias names for columns from a custom query that is not just a regular name ( sum("Sales") + 1, would not qualify as a regular name in this example) .

The query above should instead be written as

select sum("Sales") + 1 as "AdjustedSales" from "SomeTable" group by "Region". This will allow the alias "AdjustedSales" to be used if it is called in an external system.