Advanced SQL Examples
Augmenting Performance History Data
A customer wishes to augment the performance history data used by a workflow with usage data read from an external file. This can be done by carrying out the following steps:
- Use the Get Performance History Data action to extract application statistics from SAP into a dataset named ‘Application Statistics’.
- Create an External Data Source for the file and use the Read External Data Source action to read it into a dataset named ‘Used Objects’.
- Add an Execute SQL action to the workflow. Set its Source1 parameter to ‘Application Statistics’ and its Source2 parameter to ‘Used Objects’. Create a Result dataset for the action, and set the Options parameter to ‘CopyRows Source1 Result’.
- Add the following to the Execute SQL action’s Statements parameter:
INSERT INTO Result SELECT TYPE, NAME, 'External', COUNT FROM Source2
DELETE FROM Result WHERE ACCOUNT='External' AND EXISTS(SELECT NAME FROM Source1 WHERE Result.NAME=Source1.NAME)
- The ‘CopyRows Source1 Result’ option copies the Application Statistics dataset to the Result dataset.
- The first SQL statement augments the Result dataset with rows from the Used Objects dataset. In the Result dataset, these rows have their ACCOUNT column set to ‘External’.
- The second SQL statement removes any duplicates from the Result dataset by deleting external data that also exists in the Application Statistics dataset.
The following SQL statement achieves the same results, and is more efficient:
INSERT INTO Result SELECT TYPE, NAME, 'External', COUNT FROM Source2 WHERE NOT EXISTS(SELECT NAME FROM Source1 WHERE Source2.NAME=Source1.NAME)
Conditional Table Filtering
A customer wishes to filter a set of changing objects to include only those objects that were present in a filter table. However, if there are no rows in the filter table, the customer would like to copy all the changing objects into a result dataset.
Assuming that the Changing Objects and Filter Table datasets both have NAME and TYPE columns which are used as the basis for a match, this can be done as follows.
- Add an Execute SQL action to the workflow. Set its Source1 property to the Changing Objects dataset, and the Source2 property to the Filter Table dataset. Create a Result dataset for the action, and set the Options parameter to ‘MakeTable Source1 Result’.
- Set the action’s Statements property to the following. Each statement should be stored as a separate String List element.
INSERT INTO Result SELECT * FROM Source1 WHERE (SELECT COUNT(*) FROM Source2) = 0
INSERT INTO Result SELECT s1.NAME, s1.TYPE FROM Source1 s1 INNER JOIN Source2 s2 ON (s1.NAME = s2.NAME AND s1.TYPE = s2.TYPE)
These statements either copy or filter the Changing Objects dataset, depending upon whether the Filter Table dataset is empty. The results are stored in the Result dataset.