Handling '?' character in custom SQL queries

Handling '?' character in custom SQL queries

When using custom SQL in a data base connector the questionmark '?' character is used to set a parameter and if you want to use a string that contains a '?' in the custom query you need to change the SQL slightly compared to when using it directly in the data base.

If you want to run an expression similar to this: SELECT 'One?Test' + Column1 AS NewColumn FROM Table1

You have a string that is 'One?Test' that you want to concatenate with Column1 and create a new column that you call NewColumn, if you add this to the custom query and click verify you will get an error similar to this:

External error:
SELECT * FROM ( SELECT 'One?Test' + Column1 AS NewColumn FROM Table1) AS [SpotfireCustomQuery1] WHERE 1 = 0

Submitted query:
The following query parameters have not been declared:
 - 'Test'




  Adjust the SQL so that you have a single quote directly after the '?' character so in this example you can use:

SELECT 'One?' + 'Test' + Column1 AS NewColumn FROM Table1

This will enable you to run the SQL without declaring a parameter.