Guidelines for SQL in WFA

You must be aware of the guidelines for using SQL in OnCommand Workflow Automation (WFA) to write SQL queries for WFA.

SQL is used in the following places in WFA:

Guidelines Example
SQL reserved keywords must be in uppercase characters.
SELECT
    vserver.name 
FROM
    cm_storage.vserver vserver
Table and column names must be in lowercase characters.

Table: aggregate

Column: used_space_mb

Separate words with an underscore (_) character. Spaces are not allowed. array_performance

Table name is defined in singular. A table is a collection of one or more entries.

"function", not "functions"
Use table aliases with meaningful names in SELECT queries.
SELECT
	vserver.name
FROM
	cm_storage.cluster cluster,
	cm_storage.vserver vserver
WHERE
	vserver.cluster_id = cluster.id
	AND cluster.name = '${ClusterName}'
	AND vserver.type = 'cluster'
ORDER BY
	vserver.name ASC
If you have to refer to a filter input parameter or user input parameter in a filter query or user query, use the syntax as '${inputVariableName}.

You can also use the syntax to refer to a command definition parameter in reservation scripts and verification scripts.

SELECT
	volume.name AS Name,
	aggregate.name as Aggregate,
	volume.size_mb AS 'Total Size (MB)',
	voulme.used_size_mb AS 'Used Size (MB)',
	volume.space_guarantee AS 'Space Guarantee'
FROM
	cm_storage.cluster,
	cm_storage.aggregate,
	cm_storage.vserver,
	cm_storage.volume
WHERE
	cluster.id = vserver.cluster_id
	AND aggregate.id = volume.aggregate_id
	AND vserver.id = voulme.vserver_id
	AND vserver.name = '${VserverName}'
	AND cluster.name = '${ClusterName}'
ORDER BY
	volume.name ASC
Use comments for complex queries. Some of the supported comment styles in queries are as follows:
  • "--" until the end of the line

    A space is mandatory after the second hyphen in this comment style.

  • From a "#" character until the end of the line
  • From a "/*" to the following "*/"sequence
/*
multi-line
comment
*/
--line comment
SELECT
	ip as ip, # comment till end of this line
	NAME as name
FROM --end of line comment
	storage.array