Search the Site

Sponsors

bottom corner

Using dummy table to select pseudocolumns

This note illustrates how to use a dummy table to select pseudocolumns in Oracle and DB2 databases.

This page is filed under keyword(s): oracle, db2.

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.

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle, db2.
Author: C. Peter Chen
Last updated: 6 Aug 2010

bottom corner