In many database systems you can perform a select without from, for
exapmle "SELECT 1" but DB2 doesn't allow this. In others like Oracle,
you can use dummy in-memory tables like "SELECT 1 FROM dual".

How can you do that in DB2?

On 2011-05-10 21:14, M N wrote:
values 1

or:

select 1 from sysibm.sysdummy1

or even:

select 1 from dual

but you will have to enable Oracle compability mode for that


/Lennart

Shoud you be on Express-C (no Oracle compatibility), you could do the
same by creating an alias called DUAL on the sysdummy1 view (not
tested though).

--
Frederik Engelen

BTW, VALUES 1 is actually ANSI SQL.
A mystery to me why no(?) other vendor supports VALUES in its full form
since it is very powerful



--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

On 2011-05-10 23:24, Frederik Engelen wrote:
It appears as if there already is an alias:

db2 "create public alias dual for sysibm.sysdummy1"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "SYSPUBLIC.DUAL" of type "ALIAS". SQLSTATE=42710

I guess that db2set DB2_COMPATIBILITY_VECTOR=2 somehow makes this
accessible without having to qualify it with schema ( can't investigate
because I only have express on 9.7 )

/Lennart