Using variables in SQL queries

You can use variable objects in an SQL query as well as string values. To use variables in a SQL query, the query must be written as a formula, starting with the equals (=) sign and containing strings concatenated with the ampersand (&).

You can use variables in place of a table name or combine them with the WHERE clause, in place of filter values.

Using a variable for the table name

Instead of specifying an explicit table name, you could create a variable to hold a table name and run a select statement on the variable:

="SELECT * FROM "&GlobalVariables.TableName.Text

This is equivalent to:

SELECT * FROM [TableName]

Using a variable for a filter value

You could create a variable to hold a product name:

="SELECT * FROM Products WHERE ProductName =’"&
		GlobalVariables.ProductName.Text&"’"

This would be equivalent to this Select from the Products table:

SELECT * FROM Products WHERE ProductName= ’Widget’

Where a variable returns a string value, it must be enclosed in single quotation marks (’). Where the variable returns a numerical value this is not necessary. For example:

="SELECT * FROM Products WHERE ProductID="&
		GlobalVariables.ProductID.Text

Database-specific syntax

There are differences in the way in which database providers implement SQL. Relational lists are defined by SQL statements. So, report designers may have to write several versions of the same statement to ensure that the list definition will work with different types of data connection.

If you use variables in SQL queries, the syntax required depends on the type of database for which the query is written. For example, this statement is written in standard SQL:

=" SELECT Id, AccCde FROM LdgA where AccCde like '"&GlobalVariables.GV_WhereClause.Text&"' "

For an Oracle database, the statement would be:

=" SELECT ""Id"", ""AccCde"" FROM DES.LDGA where ""AccCde"" like '"&GlobalVariables.GV_WhereClause.Text&"' "

For a DB2 database, the statement would be:

=" SELECT ""Id"", ""AccCde"" FROM ADMIN_DEV.""LdgA"" where ""AccCde"" like '"&GlobalVariables.GV_WhereClause.Text&"' "