[Imported] Embedded SQL Statement Within ST Query

[sbeadle]

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: florian, originally posted: 2018-10-24 21:06:14 Id:205
This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.

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

**_NIWTelemetry:_**
**_Hello, Looking for some assistance._**

**_I am trying to develop a Structured Text logic program to_**

**_1 identify analogue points related to certain geographic areas by SQL query_**

**_2 disable the alarms on those points via a Method_**

**_grateful for any assitance or examples to get me started. I imagine there is a Type / End_Type block at the top of the ST with a SQL statement producing a record set then a DO statement looping through the record set calling the method._**

**_Thanks_**

-----

AWoodland:
You'll need two logic programs chained together.

The first logic program has the %S in with the query to get the list of the FullName that you're interested in, loop through the returned data and call the execute method on the second logic program. The second logic program (execute on interval, with interval of 0) takes the fullname as an input parameter, uses that input to define the method, i.e. Something AT %M({FullName}.Method); and then call that method.

The first level program would execute once, the second level one would execute multiple times all triggered from the first one (depending on how many points to process you may need to increase the max queue size on the second logic program).

Depending on how you want to control the geographic reason you can pass that in as an input to the first logic program

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

bevanweiss:
I think this would be possible with just one query.

_TYPE_
_AlarmObject : DATABASE_OBJECT(CAlarmObject)_
_DisableAlarms : METHOD (DINT);_
_END_TYPE;_

_PROGRAM HereLayDragons_

_VAR NOCACHE_
_MyObjects AT %S(SELECT O.Id FROM CAlarmObject O WHERE O.GEOPosition WITHIN (SELECT R.GeoRegion FROM _CGISRegion R WHERE R.FullName = 'MyRegion')) : RESULTSET OF AlarmObject;_
_END_VAR;_

_WHILE MyObjects.Valid DO_
_MyObjects.Value.DisableAlarms(0);_
_END_WHILE;_

_END_PROGRAM;_

Note: This code is untested.. and also there's this warning...
UNEXPECTED EQUIPMENT OPERATION

ClearSCADA is not suitable for controlling safety-critical systems and must not be used unilaterally to do so. ClearSCADA is not tested for, nor has any approval for use in, the control of safety-critical systems. Safety-critical systems should be controlled by an approved safety-critical platform that is installed independently of ClearSCADA.

The configuration of this product requires expertise in the design, configuration and programming of SCADA control systems. Only persons with such experience should be allowed to configure ClearSCADA. Only persons with the appropriate programming experience should be allowed to write any scripts, Logic programs, expressions, or SQL queries intended for use in ClearSCADA.

Failure to follow these instructions can result in death, serious injury, or equipment damage.

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

**_NIWTelemetry:_**
**_Thank you - very useful!_**

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

**_NIWTelemetry:_**
**_Thank you all for your comments!_**