Invalid query error using Create base view from query

Applies to: Denodo 7.0 , Denodo 6.0 , Denodo 5.5 , Denodo 5.0
Last modified on: 13 Mar 2018
Tags: JDBC data sources

Download original document

You can translate the document:

Content

Virtual DataPort allows connecting to databases using JDBC data sources. Once the connection to the database is set base views can be created in VDP. These base views can be created in two ways:

  • Importing a database table directly.
  • Creating a view by executing a SQL query on the database.

When using the second option, the SQL query will be sent to the database without modifications unless interpolation variables are being used. These interpolation variables use the syntax @VARIABLE or @{VARIABLE} like in the following examples:

SELECT * FROM internet_inc WHERE state = ‘@OPTION’

SELECT * FROM internet_inc WHERE state = ‘@{OPTION}’

It is important to keep in mind that the characters “\”, “^”, “@”, “{“ and “}” are special characters for VDP so they need to be escaped with the “\” character when creating the base view if they are not being used to define interpolation variables.

SQL comments like lines starting with “--” must be deleted. To avoid errors on execution time when applying some optimizations the semicolon “;” at the end of a SQL query, if any, must be removed too.

After receiving an “Invalid query error” when creating a base view using a SQL query take into account these considerations in case some characters need to be escaped or removed.

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training