Applies To:
  • CitectSCADA
  • VijeoCitect
Summary:
 SQL Aggregate functions are listed as follows:
- Max/Min
- Count (*) or Count(FieldName)
- SUM
- AVG
- LIST

When using any of these aggregate functions in SQL statement and executed through cicode (Using SQLExec function), extracting the value from the results using SQLGetField() function produces error "Field Not Found".

For example, the following table (named TABLE1) exist in the database:
-------------------
No         Value
1           14
2           19
3           5
4           10
-------------------

And this cicode is being run (assuming ODBC connection has been established):

---------------
String sData;
SQLExec(hSQL, "SELECT MAX(VALUE) FROM TABLE1");
SQLNext(hSQL);
sData = SQLGetField(hSQL, "VALUE");
Message("Max Value is ",sData,0);
---------------

The message will return no value and error of "field not found occurs".

Why this happens and how can we get the return value properly?

Solution:
 By SQL Default, Using any SQL aggregate function will produce return results without column name (i.e. Blank).

Therefore, the error of "field not found" occurs although it seems that we are trying to get value from the correct field.

Normally, this can be remedied by using SQL Aliases to rename the return result's column name into something else.
(for more info about SQL Aliases, follow this link http://www.sql-tutorial.net/SQL-Aliases.asp)

However, CitectSCADA DOES NOT support the usage of SQL Aliases (Read KB Q3219).

Within Citect, blank columns name will default to fieldx (where x is the number of column which has no name, increase respectively). Thus, as according to KB Q3219, we can get the value from the table above as follows:

---------------
String sData;
SQLExec(hSQL, "SELECT MAX(VALUE) FROM TABLE1");
SQLNext(hSQL);
sData = SQLGetField(hSQL, "Field1");
Message("Max Value is ",sData,0);
---------------
Keywords:
 Aggregate function, SQL, MAX, MIN, SUM, COUNT, AVG, LIST, SQLGetField, Aliases

Attachments