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:
|
/* 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 |