Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Select without from

1,048 views
Skip to first unread message

M N

unread,
May 10, 2011, 3:14:01 PM5/10/11
to
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?

Lennart Jonsson

unread,
May 10, 2011, 3:31:16 PM5/10/11
to

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

Frederik Engelen

unread,
May 10, 2011, 5:24:41 PM5/10/11
to
> select 1 from dual
>
> but you will have to enable Oracle compability mode for that

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

Serge Rielau

unread,
May 11, 2011, 7:17:00 AM5/11/11
to
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

Lennart Jonsson

unread,
May 11, 2011, 11:53:08 AM5/11/11
to

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


0 new messages