SQL VARA object references are not correctly resolved

Products

• CA Automic Workload Automation - Automation Engine
• AUTOMIC WORKLOAD AUTOMATION

Issue/Introduction

Error Message :
N/A

References of VARA.SQLI / VARA.SQL / VARA.SEC_SQLI / VARA.SEC_SQL with format {VARA,KEY,COLUMN} are not resolved correctly.
These references return a copy of the SQL statement rather than its result.

Investigation

Here is a simple test scenario :

1. Create a VARA.SQLI object with the statement below:
 

SELECT OH_NameFROM OH, USRWHERE OH_Idnr=USR_OH_IdnrAND OH_Name LIKE '&$USER#%'AND OH_Client = &$CLIENT#

Upon hitting the "Preview" button it returns the current AE user:
 

<Please see attached file for image>

2. Create a script object with references to this SQLI in the "Variables & Prompts" tab, like below :

<Please see attached file for image>

3. The script object's process looks like this :

<Please see attached file for image>

Results

Expected:  Both :PRINT lines should return the same result = output of the SQLI variable = the current AE user's name.

Actual:  Variable &TEST1# is correctly resolved, but &TEST2# returns the content of the SQL statement. Here is what the report looks like:

SAA/SUP
SELECT OH_Name
FROM OH, USR
WHERE OH_Idnr=USR_OH_Idnr 
AND OH_Name LIKE 'SAA%'
AND OH_Client = 1980
 |

Cause

Cause type:
Defect
Root Cause: A wrong filter condition in a {} expression leads to an invalid return value. Instead of an empty string, the latest used SQL statement is returned.

Environment

OS Version: N/A

Resolution

Update to a fix version listed below or a newer version if available.

Fix Status: In Progress

Fix Version(s):
Automation Engine 12.2.0 - Planned release date: 2018-06-19
Automation Engine 12.1.1 - Available
Automation Engine 12.0.4 - Available
Automation Engine 11.2.7 - Available

Additional Information

Workaround :
N/A