[Imported] Query List of Template Values

[sbeadle]

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: Tanquen, originally posted: 2019-07-17 00:43:50 Id:473
Like if you had a pump template with pump speed and wanted to list all the pumps speeds.

I have a list object on a screen and can query some values but not all.

This gives me a list of some of the tags in the group, Variables tags but not Internal tags in a template instance in the same group.

SELECT
"FullName" AS "~FullName", "Id", "Foreground", "Blink", "Background", "ValueFormatted", "LastUpdated", "TypeDesc", "MemoryUsage"
FROM
CVARIABLE
WHERE
"FullName" LIKE '%C-01%'
ORDER BY
"~FullName" ASC

The original template:
~Config.~ RTU Site.Direct eNET Outstation

If I use From CDBPOINT it will list the Internal tags I want **but no values**.

[sbeadle · Accepted solution]

>>Responses imported from previous forum

Reply From User: BevanWeiss, posted: 2019-07-17 00:49:02
This is a limitation of the ClearSCADA Schema. If you're putting together queries like this, then you should get familiar with it 🙂

What you'll need is a UNION for what you want.

SELECT FullName, Id, Foreground, Blink, Background, ValueFormatted, LastUpdated, TypeDesc
FROM CVariable
UNION CDBPoint
Where FullName LIKE '%C-01%%
ORDER By FullName ASC

If you're looking in a template, then it will have no values. It's a template (an 'abstract' thing, not a representation of anything in the real world).

Reply From User: Tanquen, posted: 2019-07-17 00:59:44
This gives me the two types of tags but only if I remove the ValueFormatted and LastUpdated columns. Both tag types are part of the same template but I can only see the values of the Variables (yellow) and not the Internal (Dark Blue)?

Don't need to even see the Variable type I just noticed an example that would let me see the Variable type tags and their values.
____________________________________________
Looks like CurrentValueFormatted works. 🙂

Reply From User: BevanWeiss, posted: 2019-07-17 03:16:32
I didn't have the schema infront of me, I was hoping that ValueFormatted would exist for CDBPoint also (and would have mapped to CurrentValueFormatted internally).

If you want them both then you may have to do sub-queries.
SELECT Id, FullName, ValueFormatted
FROM
CVariable
UNION
SELECT Id, FullName, CurrentValueFormatted As "ValueFormatted"
FROM CDBPoint

^^ untested, there may be ClearSCADA QP limitation which makes this not so workable (I can't remember if I've tried using the SELECT beneath a UNION... I would have thought so, but memory and old age 👅 ).

Reply From User: sbeadle, posted: 2019-07-17 08:04:29
Check the SQL guide. You'll see that UNION can be done in two ways. One as a list of tables, the other as a list of queries - each separated by UNION.

Reply From User: Tanquen, posted: 2019-07-17 15:37:00
I'll keep the UNION option in mind if I need to list the different types together.

Any idea why the different tables (CVARIABLE or CDBPOINT) show in different colors in the list object? Can I just make them all black text?

Reply From User: BevanWeiss, posted: 2019-07-19 09:10:16
The different colours are because in your query your telling it to use different colours (with Foreground, Background, Blink). Leave them out and they will be black on white (default). You can set these to any RGB number you want (R*256*256+G*256+B) [R,G,B between 0 and 255]

Reply From User: Tanquen, posted: 2019-07-19 15:18:19
Thanks. I had taken those out but it just went from pink to red. I'll see if I can find how to add the RGB number in.

Ok, removed the "Foreground, Background, Blink" and then set the TextColour to black in the list objects animation. Not sure why "Foreground, Background," make different stuff different colors or why removing them still gets you red but oh well.

Reply From User: BevanWeiss, posted: 2019-08-06 02:51:41
Foreground and Background make stuff different colours because it's the special way ViewX handles query results with these column labels.
This is detailed within the help.
It makes sense if you want to easily animate the list colours (which I normally do) for objects. It's very handy to show if an outstation in a list is disconnected, or has a certain alarm against it etc etc. With the 'native' colour from the CS database configuration (i.e. Colour Palette, and Alarm Severities).

Reply From User: Tanquen, posted: 2019-08-08 23:40:18
Ok, it's a bit more complicated. It was very hard to find everything but'

The sites have a screen that they all use and it's linked to a set of real or indirect tags. So when you select a certain site a script in that sites object runs and gets the values from the correct PLC and puts them into retentive script variables that are then temporally linked to the real tags that are linked on the screen.

Variables like these:
VAR RETAIN
AO_ConfigID1: REAL;

End up in something like this:
CFG_AI_01_Eng_Max

I spoke with the guy that setup this craziness and he doesn't think you can query the script variables. He said they did this because of some kind of limitation on the total number of tags. I'm guessing you would need to add a real tag into each of the site objects if you wanted to be able to query all the sites and see the value?

I was able to find all the objects with these tags using this query but have no idea how to get at the retentive script variables in each.

SELECT
"FullName" AS "~FullName", "Id", "Foreground", "Blink", "Background", "ValueFormatted", "LastUpdated", "TypeDesc", "MemoryUsage"
FROM
CVARIABLE
WHERE
( "~FullName" LIKE '%.CFG\\_VENTURI%' ) AND ( "ValueFormatted" = 'True' )
ORDER BY
"~FullName" ASC

Reply From User: Tanquen, posted: 2019-08-12 01:42:17
"Your terminology of 'retentive script variables' is incorrect. You are refering to a RETAINed variable within a Logic Program. Logic != Script."

I could not disagree less. Why do you do stuff like this?

Reply From User: BevanWeiss, posted: 2019-08-12 04:22:27
[at]Tanquen said:
I could not disagree less....

What do you disagree with?
You understand the difference between 'script' and 'logic' within the ClearSCADA system right?
 pn/e8zo9xejd3u0.png "")
 km/acvnkw1i7tsh.png "")

They behave very differently, and there are a number of critical differences to them.
The distinction between them is very important.

Attached file: (editor/pn/e8zo9xejd3u0.png), Logic.png File size: 9177

Attached file: (editor/km/acvnkw1i7tsh.png), Script.png File size: 9297

Reply From User: du5tin, posted: 2019-09-27 14:15:23
Ick... sounds like they tried to save a few dollars on the point license or support and spent way more on configuration.

Definitely look into setting things up with proper points and not variables.

The DataSets/DataRows do work wonders! You can combine data from different sources and put it in a custom table that is easy to query and work with. This includes values from variables, parameters, points, configuration and more! If there is any list style navigation we use DataSets/DataRows.