SQL Expressions

Home > Connector > Data Expressions > SQL Expressions

Using SQL in functions in Expressions

Expressions can be modified to include SQL functions and operations. To do this, type in the SQL syntax into the Expression Source property and change the Expression Type to SQL Expression. The SQL functions available are specific to the ODBC driver used by the connection type for the container. For information on the available functions for a connection type see the Help files or documentation provided for the driver.

As an example you may have a text field in a table called AccountNo which is a seven digit code. The first three digits may be significant as they signify a high level Main Account while the last four digits signify a Sub Account. To strip the Main Account out from the AccountNo you can create an expression that simply takes the first three characters from the AccountNo. First add a new expression selecting the AccountNo field. Select the new field and rename it "Main Account". Then uncheck the "This is a Base Field" property and modify the Expression Source.

For a SQL Server connection type the syntax would be:

SUBSTRING([table].[field],1,3)

The syntax for this example for some other systems are shown below:

• Pervasive:

  SUBSTRING("LedgerMaster"."AccountNo",1,3)

• Access:

  LEFT([LedgerMaster].[AccountNo],3)

• SQL Server:

  SUBSTRING([LedgerMaster].[AccountNo],1,3)

Some of the most common uses for SQL functions are:

• Stripping out parts of a text field ( shown in the above example).
• Casting one data type to another (e.g. a Date stored as a text field could be cast to a real date).
• Performing mathematical operations (e.g. Summing two fields).
• Returning a certain value based on some logic (e.g. Returning the text YES whenever a certain boolean field is equal to 1).

Note that you cannot use an expression name within a SQL expression, you have to use the expression source.