Using dummy table to select pseudocolumns

In Oracle, the dummy table is “dual”. It is present in all Oracle database installations, thus no special setup is needed to use it. Below are some samples on how to use this table.

-- Getting the current system date:

PROMPT> select sysdate from dual;

SYSDATE
---------
06-AUG-10

-- Getting the current logged-in user:

PROMPT> select user from dual;

USER
------------------------------
YTCDBA

-- Display the result of some string manipulation

PROMPT> select substr('hello world',1,7) from dual;

SUBSTR(
-------
hello w

The DB2 equivalent of Oracle’s “dual” is “sysibm.sysdummy1”. Below is a sample of the usage.

-- Getting the current system date:

PROMPT> select current date from sysibm.sysdummy1;

00001
----------
2010-08-06

-- Getting the current logged-in user:

PROMPT> select current user from sysibm.sysdummy1;

00001
-----
USER1

-- Display the result of some string manipulation

PROMPT> select substr('hello world',1,7) from sysibm.sysdummy1;

00001
-------
hello w

It is also worthy of note that MySQL and SQL Server can select pseudocolumn data without the use of a dummy table. For example, in SQL Server, you can get the current date by running “select getdate();” without a “from” clause in your SQL statement.