Using Pseudo Columns in Recital 9

In this article Yvonne Milne details on how to use Pseudo Columns in Recital 9 to return extra information about row sets.

Yvonne Milne
May, 2005

Overview

A Pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but they can not be updated. They provide extra information about a row set.

Pseudo Columns	Description
CURRVAL	The CURRVAL pseudo column will return the current sequence number from the specified table. Sequence numbers can be used for primary and unique index keys.
NEXTVAL	The NEXTVAL pseudo column will return the next unique sequence number from the specified table. Sequence numbers can be used for primary and unique index keys.
ROWID	The ROWID pseudo column will return a number identifying the row's physical stored position in the table. The ROWID pseudo column can be used to perform singleton selects, or optimize updates of a known ROWID.
ROWNUM	The ROWNUM pseudo column will return a number indicating the order in which the rows are selected from the table.
SQLCNT	The SQLCNT pseudo column will return the number of rows affected by the last SQL statement. For example, after a SELECT statement, SQLCNT will contain the number of rows selected.
SQLCODE	The SQLCODE pseudo column will return a number indicating the result of the last SQL statement.
SYNCNUM	The SYNCNUM pseudo column will return the unique sequence number assigned to a row from the specified table.

CURRVAL

Purpose
Return the current sequence number from the specified table

Description
The CURRVAL Pseudo Column will return the current sequence number from the specified table. Sequence numbers can be used for primary and unique index keys.

Example
// config.db
set sql to recital
set sql on
// end of config.db

CREATE TABLE cust (acc_num INT , acc_name char(20));
INSERT INTO cust (acc_num, acc_name) VALUES (NEXTVAL, "Smith");
INSERT INTO cust (acc_name) VALUES ("Brown");
INSERT INTO cust (acc_num, acc_name) VALUES (CURRVAL+2, "Jones");
SELECT * from cust;

NEXTVAL

Purpose
Return the next unique sequence number from the specified table

Description
The NEXTVAL Pseudo Column will return the next unique sequence number from the specified table. Sequence numbers can be used for primary and unique index keys.

Example
// config.db
set sql to recital
set sql on
// end of config.db

CREATE TABLE cust (acc_num INT , acc_name char(20));
INSERT INTO cust (acc_num, acc_name) VALUES (NEXTVAL, "Smith");
INSERT INTO cust (acc_name) VALUES ("Brown");
INSERT INTO cust (acc_num, acc_name) VALUES (CURRVAL+2, "Jones");
SELECT * from cust;

ROWID

Purpose
Return a number identifying the row's physical stored position in the table

Description
The ROWID Pseudo Column will return a number identifying the row's physical stored position in the table. The ROWID Pseudo Column can be used to perform singleton selects, or optimize updates of a known ROWID.

Example
// Optimized update accounts row 35 with a 15% commission charge
EXEC SQL
UPDATE accounts
SET ord_value=ord_value*1.15, due_date = date()+30
WHERE ROWID=35;

ROWNUM

Purpose
Return a number indicating the order in which the rows are selected from the table

Description
The ROWNUM Pseudo Column will return a number indicating the order in which the rows are selected from the table.

Example
// Display all overdue accounts with 15% commission in
// Sorted "name" and "paid date" order with the row number.
EXEC SQL
SELECT ROWNUM, name, address, balance, cost*1.15
FROM accounts
WHERE paid_date < date()
ORDER BY name, paid_date;

SQLCNT

Purpose
Return the number of rows affected by the last SQL statement

Description
The SQLCNT Pseudo Column will return the number of rows affected by the last SQL statement. For example, after a SELECT statement, SQLCNT will contain the number of rows selected.

Example
// Display all overdue accounts with 15% commission in
// Sorted "name" and "paid date" order with the row number.
EXEC SQL
SELECT ROWNUM, name, address, balance, cost*1.15
FROM accounts
WHERE paid_date < date()
ORDER BY name, paid_date;

SQLCODE

Purpose
Return a number indicating the result of the last SQL statement

Description
The SQLCODE Pseudo Column will return a number indicating the result of the last SQL statement.

SQLCODE return values:

Example
// Display all overdue accounts with 15% commission in
// Sorted "name" and "paid date" order with the row number.
EXEC SQL
SELECT ROWNUM, name, address, balance, cost*1.15
FROM accounts
WHERE paid_date < date()
ORDER BY name, paid_date;

// Check return code and number of rows returned
EXEC SQL
SELECT DISTINCT sqlcode, sqlcnt from accounts;

SYNCNUM

Purpose
Return the unique sequence number assigned to a row from the specified table

Description
The SYNCNUM pseudo column will return the unique sequence number assigned to a row from the specified table.  Each new row inserted into a table will be assigned a unique sequence number for that table. Even if the row is deleted later or if all the rows are deleted from the table, that number will not be issued again.

Note: The SYNCNUM pseudo column for existing Recital 9 tables can be populated using the dbconvert utility and the CONVERT command.

Example
// Display all overdue accounts with 15% commission in
// Sorted "name" and "paid date" order with unique row sequence number.
EXEC SQL
SELECT SYNCNUM, name, address, balance, cost*1.15
FROM accounts
WHERE paid_date < date()
ORDER BY name, paid_date;