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: [email protected] [mailto:[email protected]] Im Auftrag von Lukas Eder Gesendet: Donnerstag, 12. April 2012 12:02 An: [email protected] Betreff: Re: Select from dummy/empty table 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 <[email protected]>: > 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 > >
