SQL VARA object references are not correctly resolved

Document ID : KB000084630
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
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:
 
0EM0N000001hnLK.png
2. Create a script object with references to this SQLI in the "Variables & Prompts" tab, like below :
0EM0N000001hnKR.png

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

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

 |
Environment:
OS Version: N/A
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.
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