You can translate the question and the replies:

"Allow literal as parameter" setting and datatype mismatches

We are using a connection to HANA and running into the following error when aggregating some decimal data type fields using the coalesce function: select year_quarter, item_name, sum(quantity) quantity, sum(amount) amount from ( SELECT year_quarter, item_name COALESCE(quantity, 0) as quantity, COALESCE(amount, 0) as amount FROM design.hana_view ) z group by 1, 2 ERROR: Received exception with message 'SAP DBTech JDBC: [266]: inconsistent datatype: only numeric type is available for SUM/AVG/STDDEV/VAR function The "Allow literal as parameter" is set to "Yes (default)" when receiving this error. The execution trace shows the zero in the coalesce coming in as a parameter value. When setting "Allow literal as parameter" to "No", the query above works just fine. In the execution trace, the zero in the coalesce is embedded in the script as expected. Can someone explain a bit more in depth what the "Allow literal as parameter" setting does? And why it would cause this inconsistent datatype issue when set to "Yes"? Also, what impact would it have on queries if it was set to "No"? Is there a degredation in performance when set to "No"?
user
31-03-2023 16:37:57 -0400
code

1 Answer

Hi, I’ve checked [this section](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/data_source_configuration_properties) of the documentation and it explains that the property: “Allow literal as parameter” defined with “yes”, indicates that the source allows the literals to be indicated as parameters of the Prepared Statement created to execute the view. If “no”, the Server generates the query with the literals in it. I’d recommend keeping this setting with the default value as using Prepared Statements helps reusing the underlying data source reusing the execution plan of the query in different executions. Changing this value might reduce the performance of the query for certain data sources due to a new execution plan being generated for every query. Hope this helps!
Denodo Team
03-04-2023 06:03:07 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here