Select from dummy/empty table

[Schönfisch, Jörg]

Hi everybody,

 

I’m creating some queries similar to this one:

SELECT CASE WHEN EXISTS (SELECT * FROM table ) THEN 'yes' ELSE 'no' END;

This query runs on Postgres without the FROM clause for the outer SELECT, however, on Oracle I have to add „FROM dual“ to make it work.

 

It would be nice to have a method, e.g. in SelectQuery, like addFromDummyTable which adds nothing for Postgres, „FROM DUAL“ for Oracle and something similar for other dialects. MySQL also seems to support DUAL. Currently I’m manually adding something like „FROM (SELECT 1) as dummy“ to each query.

 

I can try to come up with an implementation for that if you’re interested in this.

 

Cheers,

Jörg

 

[Lukas Eder]

Hi Jörg,

jOOQ omits the FROM clause where this is possible without restriction.
This applies to Postgres, Sybase ASE, SQLite SQL Server. Otherwise,
jOOQ will always render "from dual" if the query syntax needs this. Or
any of these

- from db_root for CUBRID
- from SYSIBM.DUAL for DB2
- from SYSIBM.SYSDUMMY1 for Derby
- from SYS.DUMMY for Sybase SQL Anywhere
- from INFORMATION_SCHEMA.SYSTEM_USERS for HSQLDB
- from (select 1 as dual) as dual for Ingres

Just do

for (SQLDialect dialect : SQLDialect.values()) {
System.out.println(dialect + ":" + new Factory(null,
dialect).selectOne().getSQL());
}

to see the difference.

For more insight, please read this blog post:
http://blog.jooq.org/2011/10/16/sql-trouble-with-dummy-tables/

Am 12. April 2012 11:50 schrieb Schönfisch, Jörg
<joerg.sc...@softplant.de>:

[Schönfisch, Jörg]

Thanks for the detailed reply!
Somehow I missed that. Searching for "dummy table" in the first place instead of "empty table / from" would have brought up that post as first hit. I need to improve me Google skills :)

Now I'm even more amazed by your work than I was before, jOOQ is awesome!

Cheers

-----Ursprüngliche Nachricht-----
Von: jooq...@googlegroups.com [mailto:jooq...@googlegroups.com] Im Auftrag von Lukas Eder
Gesendet: Donnerstag, 12. April 2012 12:02
An: jooq...@googlegroups.com
Betreff: Re: Select from dummy/empty table

[Lukas Eder]

> Thanks for the detailed reply!
> Somehow I missed that. Searching for "dummy table" in the first place instead of "empty table / from" would have brought up that post as first hit. I need to improve me Google skills :)

I guess that depends on "where you come from". When you're used to
Oracle, DUAL seems as natural as anything. When you're used to other
databases you probably don't think about dummy tables. When you're
strictly following the SQL standard, then you'd expect the FROM clause
to be mandatory only within a <table expression>

In the end of the day... SQL can be confusing :-)

> Now I'm even more amazed by your work than I was before, jOOQ is awesome!

Thanks!