SQLQUERY

Description

Executes a SQL query and returns the value in the first column of the first row in the result set. Could be used for a select count(*).

Syntax

SQLQUERY ( TextSourceField, "connection", "sqlStatement" )

Parameter	Description
TextSourceField	Source value used in the SQL statement.
connection	The connection for the database to run the query against. If the connection is Scribe Internal Database, the SCRIBE user must have at least SELECT privileges on any table referenced in the SQL statement.
sqlStatement	The SQL statement to execute.

Remarks

The SQL statement must be less than 255 characters.
Use %s inside of the query to send a value from the source. If the source data type is text, use '%s'.
If the database was configured as <table>.dbo.<fieldname>, you must include dbo. in the query. See your database administrator for more information.
Because %S is a reserved character pair, if you are executing a SQLQUERY function and using a where clause that contains the %S character sequence, use the SQL CONCAT function to create that sequence.

Examples

Numeric Source

If source field (S1) is numeric, and the connection name is Target:

SQLQUERY (S1, "Target", "select name from account where accountid = '%s'")

Text Source

If source field (S2) is text and the connection name is Target:

SQLQUERY (S2, "Target", "select accountid from account where name = '%s'")

Database Table

If the database was configured in the format (<table>.dbo.<fieldname>), and the connection name is Target, .dbo is required (the table name is defined with the target and is not needed):

SQLQUERY(S1 , "Target", "select accountid from dbo.account where name = '%s'")

Where Clause With %S

If you are selecting a value that is like %SQL in the where clause of a SQLQUERY command, use the SQL CONCAT function to create the %SQL string within that clause as follows:

SQLQUERY( S1, "TARGET", "select VERSION from ACCOUNTDBS where DBTYPE like CONCAT('%', 'SQL') and ACCOUNTSTATUS='%s'")

In this example, if the value for S1 is Open, the query that is executed after Insight substitutes the value into %s is:

"select VERSION from ACCOUNTDBS where DBTYPE like CONCAT('%', 'SQL') and ACCOUNTSTATUS='Open'"

Using SQLQUERY with datetime data:

If the source field contains a date value, you must wrap the source field with a FORMAT function. For example, if the source field (S3) is a datetime field the following function will not work:

SQLQUERY(S3, "Target", "select paid from dbo.account where paid = '%s'")

Instead, use the function as follows:

SQLQUERY(FORMAT(S3,"yyyy-mm-dd hh:mm:ss"), "Target", "select paid from dbo.account where paid = '%s'")