Contents   Introduction   Concepts   User Help   Modeler Help   Browser Help
Administrator Help   Developer Help    Utility Help   Visio   Graphical Modeler

Using SQL queries in Calc Fields

This section describes the use of SQL Queries in calc fields.  This feature will be demonstrated by means of a practical example which requires some preparation of the environment before it will work.

Once you have completed the preparation, open the Item Maintenance Browser, select the "SQLCalcTest" NodeType.

 Click on the "new" button, complete the input screen as illustrated below:

 The calc field to be entered is as follows:

 aAttrValue execSQLWithDsn: 'Garbage' userid:'' password: '' statement:
'select * from Table1 '.

Click "Create" to create the new item.

When the item is created, it will display the contents of the table as illustrated below:

 Users should be careful not to extract a table containing thousands of rows, as there are no built-in limit checks in this operation and it might take a long time to complete.

 If the SQL Statement is of such a nature that a single value is returned, EVA Netmodeler will attempt to return the value in its DBMS equivalent primitive type. For example a numeric value will be returned as a numeric and a string as a string.

 The result of the query may therefore be used in a calculation. For example, the following query will return a single numeric value:

 aAttrValue execSQLWithDsn: 'Garbage' userid: '' password: '' statement:
'select max(field1) from Table1 '.

 And the statement below uses this value in a calculation:

 (aAttrValue execSQLWithDsn: 'Garbage' userid: '' password: '' statement:
'select max(field1) from Table1 ') *200.

Any SQL statement, including INSERT, UPDATE and DELETE statements may be used in this way.