Interesting problem, and actually makes a lot of sense to me from a database behavior standpoint.
I am guessing that what is happening here is;
(1) the database engine binds the original SQL statement, and tells it there is to be one bound variable.
(2) The bound variable is applied at run time, *after* the SQL binding process.
(3) Therefore, it is too late for the injected SQL to perform as you would like.
So you'll need to see if you can compose the whole SQL statement prior to the binding process.
I have not tested this, but I wonder if you could replace this:
MySql where name in(?)
with;
MySql where name in (&sub_query#)
I suspect this idea will not work, because I don't think the variable object SQL container will resolve UC4 variables. But it might?
Another workaround that might be possible, is to do this with a batch SQL job which can modify the SQL statement prior to binding, and then invoke it from an VAR.EXEC.
------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------
Original Message:
Sent: 07-02-2020 01:50 PM
From: Ken Yong
Subject: SQL_SEC Parameter
Hi guys,
I have a query that involves subquery needs to be executed in an SQL_SEC object.
My full query is: Select organization_id from hr_operating_units where name in (Select OU from hr_Header where ID = 123)
Since my subquery needs to be generated based on certain conditions in another object, hence I will pass this subquery as a parameter
into SQL_SEC object.
My ultimate purpose is to execute a query (Constructed dynamically) and get the result.
What I did below actually not working, can anyone suggest how can I do this in Automic, please? Thank you! :)
------------------------------
Ken
TX, Houston
------------------------------