on Jun 14, 2016Article Content
| Article Number | 000030124 |
| Applies To | IMG - all versions and platforms. At the time of publishing the current release version is 6.9.1 |
| Issue | A SQL select node is a workflow component that allows the user to create custom workflow variables. The user enters a SQL query into the node, and then is able to call the results of the query elsewhere in the workflow. Configuration General Tab The general tab of the SQL Select node is the same as the general tab of all nodes. There are fields for the node’s name and description, as well as fields to set various status messages for the node. The values input into the general tab do not affect the resulting variable’s name or contents. Query Tab The query tab contains two fields, the Query field and the Variable Scope Field Query Field This is where the select statement is input. If inputting a single statement the query does not need to be terminated by a semicolon. The SQL query has a 4000 character limit, as does the value being retrieved. Variable Scope Field The Variable Scope Field determines the scope in which the newly created variable can be called. There are three options: Node level, Job Level, and Public level. Node level allows the variable to be called only in the node in which it is defined. Job level allows the variable to be called in the workflow in which it is defined. Public level allows the variable to be called in the workflow in which it is defined, as well as in any of that workflow’s parent/child processes. The name of the newly created workflow variable is a composite of the Variable Scope and the Column name/alias that is selected in the query. The scope will be lower cased, the column upper cased. ${[scope]UserData_[COLUMN or ALIAS]} Example Let’s say we want to include the submission notes field for a change request in an email; we can use a SQL Select node to capture the value and bring it into the email node. In the query tab, I input the following: select notes from pv_change_request where name = ${access_request_requestID} This SQL query will return the contents of the notes column which are associated with this change request’s number. I then set the Variable Scope on the same tab to Job level so it can be used in the rest of the workflow it was defined in. The resulting variable is ${jobUserData_NOTES}, which I can put in the body of my email node. If for some reason we need the variable to have a different name, like if we already have a jobUserData_NOTES varible, we can use an alias in the SQL query to bring in the variable named whatever we want. select notes as 'test' from pv_change_request where name = ${access_request_requestID} This would result in variable ${jobUserData_TEST} |