Invalid query error using Create base view from query

Applies to: Denodo 8.0 , Denodo 7.0 , Denodo 6.0
Last modified on: 05 Jun 2020
Tags: JDBC data sources

Download 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.

In Denodo versions previous to Denodo 8.0, 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.

In these versions, after receiving an “Invalid query error” or an “invalid character” 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.

References:

Create Base View from SQL Queries

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