Hi,
I am having a problem in which the app frontend returns an error when running a SELECT query on a capital-case column. Example of a query returning an error:
SELECT NAVIGATE from inputTable
The problem does not occur in ONE DATA’s query executor, and this problem does not happen when running a query selecting all columns of the inputTable, despite there being a capital-case column. Using a * operator is not viable in my use case though, as I need to embed the column name in a GROUP BY statement.
SELECT * from inputTable
Error’s screenshot:
Have you saved your data in Postgre? You can check that in the Data Table Save Processor.
If so, that’s most likely the problem. Postgre does not like upper case column names, so an easy solution would be to use only lower case column names (in your Workflow).
You’re probably wondering, why it is working in a Workflow and not in the App. The Query Processor uses Spark SQL, whereas the custom SQL in the App is pushed to the database and uses the SQL dialect of that database. So if you have saved your data on Postgre, you must write a Query that is valid in Postgre SQL.
1 Like
Hi Kai,
Thank you for the quick reply. I do confirm that the data was indeed saved as PostgreSQL, so I’ll make sure to use lower-case letters in the future for my tables to be used as input to apps then 
.
Alternatively, are there any formats in the Data Table save step that would support the selection of upper-case columns in apps (so via Spark SQL)?
Using upper-case columns should not be a problem even with PostgreSQL. But you must put the column names in double-quotes. Since the query will be specified inside ODML, the quotes must be escaped. Give SELECT \"NAVIGATE\" from inputTable a try.
1 Like
@stefan.ganser The escaping worked, great!