You can translate the question and the replies:

Substring Error

**version** is a column in a table called bv_table values for **version** looks like Dec[10+2], Jan[1+11]. I am trying to extract the number 10, 1 from the string, any number before +, I am running the below query in the vql shell select SUBSTRING(version, INSTR(version, '[')+1, INSTR(version, '+') ) from bv_table; It is yielding the correct results but also throwing the error message as below Finished with error: Error executing query. Total time 0.007 seconds. QUERY [VIRTUAL] [ERROR] QUERY [JDBC WRAPPER] [ERROR] QUERY [JDBC ROUTE] [ERROR] Received exception with message 'Invalid length parameter passed to the LEFT or SUBSTRING function.' When I run the the query with hardcoded string values in the queries like below select SUBSTRING('dec[12+1]', INSTR('dec[12+1]', '[')+1, INSTR('dec[12+1]', '+')) it throws no error. Trouble is when reading the string values from column.
user
29-01-2020 03:48:26 -0500

1 Answer

Hi, This error may occur when the function SUBSTRING is delegated to the data source and the length of the string value+1 is less than <startindex>. You can check if the query is delegated to the data source and more detailed information about the execution in the [Execution Trace of a Statement](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/querying_views/execution_trace_of_a_statement#execution-trace-of-a-statement). When the SUBSTRING is performed by Denodo Virtual DataPort Server directly, this error should not happen. To know how to change the delegation to the data source, please refer to the section [Data Source Configuration Properties](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/data_source_configuration_properties/data_source_configuration_properties#data-source-configuration-properties) in the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
29-01-2020 06:14:57 -0500
You must sign in to add an answer. If you do not have an account, you can register here