www.usoft.com To www.usoft.com

Pseudo-columns

  • 21 July 2022
  • 1 reply
  • 150 views
Rob.van.Haarst
Userlevel 3
Badge +2

This article is about pseudo-columns as part of the SQL syntax that USoft supports.

Pseudo-columns

A pseudo-column is an expression that you can use in the SELECT list of a SQL statement as if it were a column name, but that does not refer to a physical column in a table.

You can use pseudo-columns in SELECT output lists but also as the value of certain attributes in USoft Definer, in particular, in the Default Value attribute of Columns and Domains.

Pseudo-column Description
ROWNUM The sequence number of a given record in a query result set.
SYSDATE

The current server date.

SYSDATE is Oracle-only.

For portability to other platforms, use $$CURRENTDATE$$ or the CURRENT_DATE SQL function instead.
$$CURRENTDATE$$ The current server date.
$$CURRENTDATETIME$$ The current server date and time.
$$GUID$$ A generated GUID, same result as the GUID() SQL function.
$$TRANSACTIONDATE$$ The server date of the first time this function is processed within the transaction.
$$TRANSACTIONDATETIME$$ The server date and time of the first time this function is processed within the transaction.
USER The name of the USoft application user currently logged in.
$$RDBMSUSER$$ The name of the user used to access the RDBMS resources.
$$RDBMSOWNER$$ The name of the user who owns the RDBMS objects being accessed.
$$USER$$ The name of the USoft application user currently logged in.

Notes

Pseudo-columns are similar to SQL functions that take no arguments, such as TRANSACTION_DATE(). They differ from such functions in that the call syntax does not use empty parentheses ():

SELECT USER
SELECT $$USER$$
SELECT TRANSACTION_DATE()

Another difference is that you can use a pseudo-column, but not a SQL function, as the Default Value attribute of a Column or Domain.

Rob van Haarst

1 reply

K

Be careful when using $$TRANSACTIONDATETIME$$ as a Default Value in web applications.

Due to the mechanism in the back-end of a web application, a record is inserted in a transaction separate from the constraints that are checked. Hence, a constraint that uses the SQL function TRANSACTION_DATE() will yield a slightly different datetime.

An example is a table that logs the status of some process. The FROM column has default value  $$TRANSACTIONDATETIME$$ and the UNTIL column has default value NULL. There is a constraint that updates the UNTIL column of the older with TRANSACTION_DATE() when two records with UNTIL is NULL are found.

The expected behavior is the following:

STATUS FROM UNTIL
In Process 2024-01-17 14:22:00 2024-01-19 16:37:00
Ready 2024-01-19 16:37:00 NULL

When using a C/S application, this is also the provided result.

When using a web application, however, the following result is produced:

STATUS FROM UNTIL
In Process 2024-01-17 14:22:00 2024-01-19 16:37:03
Ready 2024-01-19 16:37:00

NULL

 

There is a slight delay between the FROM value of the new status and the UNTIL value of the old status. This discrepancy, when unaccounted for, can lead to constraints not firing on the web while they do in C/S.

Therefore, when developing an application for the web, stick to using either $$TRANSACTIONDATETIME$$ in Default Values OR TRANSACTION_DATE() in Constraints.

Reply


Terms & ConditionsCookie settings