Using c-treeSQL for Computation and Conversion

The c-treeSQL SELECT statement can be used to make computations in an ESQL program. The computations can be done using the SYSCALCTABLE table. The SYSCALCTABLE is a table with owner as admin and contains exactly one row with a single column.

Programs can use c-treeSQL SELECT statements and scalar functions to perform a wide range of computations and conversions. c-treeSQL provides a system table called SYSCALCTABLE to facilitate such operations. The SYSCALCTABLE table contains a single row, which means SELECT statements that specify SYSCALCTABLE will return at most a single row. This makes it convenient to use SELECT INTO statements for computations and conversions.

(Of course, you don’t have to use the table SYSCALCTABLE but it is guaranteed to be available in any c-treeSQL environment.)

In general, the format for using SYSCALCTABLE in this manner is as follows:

EXEC SQL

   SELECT expression

   INTO   :host_var

   FROM   admin.syscalctable ;

The expression in the SELECT list can have constants or host variables and can include scalar functions.

The following example shows some of the computations that can be done using the SELECT statement:

Example Using c-treeSQL for Computation

...

EXEC SQL BEGIN DECLARE SECTION ;

     long   result ;

     long   num ;

     long   val ;

     DATE   dtval ;

EXEC SQL END DECLARE SECTION ;

...

EXEC SQL

     SELECT (100 * 12 (1234 - 354))

     INTO   :result

     FROM   admin.syscalctable ;

EXEC SQL

     SELECT (:num * 10 + ABS ((360 - :val)))

     INTO   :result

     FROM   admin.syscalctable ;

EXEC SQL

     SELECT NEXT_DAY (TO_DATE (11/12/1992), WEDNESDAY)

     INTO   :dtval

     FROM   admin.syscalctable ;

...