[Imported] Using Indirect reference in the FROM Clause of an SQL Query

[sbeadle]

>>Message imported from previous forum - Category:Scripts and Tips<<
User: mchartrand, originally posted: 2018-10-19 17:22:05 Id:147
This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.
-------------------------------

**_Chadam:
I will like to evaluate multiple Data Tables using common code.
Extract of example code will be
VAR
Table AT %S(SELECT Name, Age, FROM TABLE1 WHERE Name Like '%Tom%'): RESULTSET OF TableExtract.
END_VAR
Is it possible to replace TABLE1 with a reference eg. "...Building1.DataTable.TableName"?_**

-------------------------------

bevanweiss:
Yes... but not directly like that.

You would instead have

VAR_INPUT
TableName : STRING;
END_VAR

VAR
Table AT %S(SELECT Name, Age FROM ? WHERE Name LIKE '%Tom%') : RESULTSET OF TableExtract WITH_PARAMS TableName;

and I assume you know that the RESULTSET datatype must be valid (i.e. you must have defined the TableExtract datatype).

___________________________

**_Chadam:
Hi Bevan. Thanks for your reply. However, the compiler flags that as an invalid Query. Doesn't seem to like the ? in place of the table name. Does it compile okay for you on your end?_**

______________

AWoodland:
Unfortunately table name cannot be passed in as a variable, there are checks during compile time that needs access to the table.

Does this function need to be in logic, i.e. server-side? If you use scripting on a mimic, which is client-side and client-driven, you can construct the query and pass it into Server.Query. That does not need to know the table when compiling so may well do what you want.

_____________________________

**_Chadam:
Hi Adam. Yes the function needs to be in Logic. If the table cannot be passed as a variable then that is extremely unfortunate. Do you know if schneider plans to incorporate this feature in future?_**

____________________________

bevanweiss:
Chadam: I'd have to admit that I didn't test it out within ClearSCADA...
My memory failed me unfortunately. I often write SQL queries outside ClearSCADA against the ClearSCADA Database, and it works there... but yeah, the ST Logic engine does have some weird requirements on the compilation.

It might be something worth raising a feature request for (with your ClearSCADA support contact). It'll never get changed if Schneider aren't told that people want it changed.

I expect there is some back end 'magic' which is significantly easier when knowing the table at compile time (it allows column validation during compile also).