Queries

Queries are used extensively in Workflows to search for data and data sets. A query is a precise request to retrieve information from a table in a database or to access information from an information system. In workflows, queries are usually performed on a layer or table resource, and target either ArcGIS or Geocortex. The result of a query is usually data in the form of a feature set. Each feature in a feature set may have a field value and/or geometry data associated with it. For tables, the feature set does not include geometries.

Queries are defined by a SQL-like WHERE clause. SQL (Structured Query Language) is the standard universal language used by most relational database management systems (RDBMS), including ORACLE, Microsoft SQL server, Informix, etc. Geodatabases usually consist of multi-tiered architecture that layers an RDBMS with a Geodatabase like ArcSDE that acts as a gateway and manages geographic data. To learn about the SQL WHERE clause, read SQL WHERE Clause at W3Schools.com.

SQL has a number of wildcard characters:

For more information, see SQL Wildcards at W3Schools.com.

Examples

The syntax in the examples below assumes the query is being run against a layer that has the following fields:

"OBJECTID>1000 AND Name LIKE 'Victoria' AND Height>5.5"

You can incorporate variables of type string and Int32 into any string-typed property in any activity, for example:

"the value of my variable is " & variable1.

To add a new line, use the Visual Basic command:

"Line 1" & Environment.NewLine & "Line 2"

To get the attribute value of a feature, use: 

queryresult.features.first().attributes("MyAttributeName").ToString()

See also...

To add list items to a Combo Box using a query:

Parameterized IN Conditions

This article illustrates how to create a parameterized IN condition with Microsoft SQL Server. However, it could be recreated with any SQL variant that allows user-defined functions. See also Create a Parameterized IN Condition with Oracle for an Oracle-specific function.

When you are doing work with very large dataset, you may want to allow users to retrieve large subsets of the data. For example, if there are 10,000 tax parcel IDs in a database, users might want to choose only 700 of them to export to an Excel spreadsheet.

With the SQL Query Activity, you could use an IN condition that targets a list of chosen parcel IDs. However, in order to make this IN condition safe in a production environment, we must work within the following constraints:

In the case that your viewer is only available to users you trust, you can use the SQL Query Unsafe Activity to bypass the need for a parameterized IN condition all together. Using the SQL Query Unsafe Activity to perform this function is a security vulnerability and we do not recommend that you use this method.

Create a User-Defined Function

In order to accept a list as a parameter, you need to configure a function in your SQL database. To learn more about adding functions to SQL Server, see Microsoft's CREATE FUNCTION (Transact-SQL) documentation.

This F_Split function allows you to use one parameter in your IN condition:

CREATE FUNCTION dbo.F_Split
(
@InputString VARCHAR(MAX)
,@Separator VARCHAR(MAX)
)
RETURNS @ValueTable TABLE (Value VARCHAR(MAX))
AS
BEGIN

DECLARE @SeparatorIndex INT, @TotalLength INT, @StartIndex INT, @Value VARCHAR(MAX)
SET @TotalLength=LEN(@InputString)
SET @StartIndex = 1

IF @Separator IS NULL RETURN

WHILE @StartIndex <= @TotalLength
BEGIN
SET @SeparatorIndex = CHARINDEX(@Separator, @InputString, @StartIndex)
IF @SeparatorIndex > 0
BEGIN
SET @Value = SUBSTRING(@InputString, @StartIndex, @SeparatorIndex-@StartIndex)
SET @StartIndex = @SeparatorIndex + 1
END
ELSE
BEGIN
Set @Value = SUBSTRING(@InputString, @StartIndex, @TotalLength-@StartIndex+1)
SET @StartIndex = @TotalLength+1
END
INSERT INTO @ValueTable
(Value)
VALUES
(@Value)
END

RETURN
END

Once this function is configured on your SQL server, you can create server activities that invoke the F_Split function in your IN clauses.

Create a Parameterized IN Condition

To allow users to input a list of records they want to retrieve, we will need to take their input string and convert it into data that the SQL server can read by using the F_Split function. In the case of parcel IDs, which are integers, we use the CAST function to to convert the result to integers as well.

WHERE ParcelID IN (SELECT CAST(Value AS INT) FROM F_Split(@groupIDs, ','))

As a result, the list of parcel IDs that a user would input ("1,5,9,15") can be processed as individual IDs and understood by the SQL server as individual parcel IDs from the database ("1", "5", "9", and "15").

You could use the F_Split function for other layers, too:

WHERE Parks IN (F_Split(@groupColors, ','))

In the same way, this takes the received string of values "Blue, Yellow, Grey" and converts it to the values to the format that the SQL server understands and can match in the database. In this case, "Blue", "Yellow", and "Grey".

Create a Parameterized IN Condition with Oracle

With Oracle, our approach to this user-defined function is different. However, this result is the same. This user-defined function breaks a string with comma-separated values into a format that the Oracle database can read.

'SELECT RouteEventID 
FROM ops.pods_subsystemrange
WHERE subsystemEventID IN (
SELECT regexp_substr(:grpIDs,'[^,]+', 1, level) from dual
connect by regexp_substr(:grpIDs, '[^,]+', 1, level) is not null )'

In this case, :grpIDs would be the parameter's name. Its value should be a string with comma-separated values, like "1,5,29,41".