The SQL above is legal and it belongs the SQL ANSI 92 as well. I have tested this SQL in Oracle, MS SQLServer, Postgres, Intersystems Caché, MySQL and Hypersonic databases.
Regards
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Well, it's certainly not easy in the current API.
is that even legal SQL?
Hi James,
The SQL above is legal and it belongs the SQL ANSI 92 as well. I have tested this SQL in Oracle, MS SQLServer, Postgres, Intersystems Caché, MySQL and Hypersonic databases.
Regards
Can i do it using the CustomSQL ?
Well, you can do anything you want with CustomSql. :)
Using the current code, you could do something like (assuming you have q1, q2, q3 as your 3 queries):
SqlObject obj = SqlObjectList.<SqlObject>create(UnionQuery.Type.UNION)
.addObjects(q1, SqlObjectList.<SqlObject>create(UnionQuery.Type.UNION_ALL)
.addObjects(q2, q3));
However, i'm planning on adding a method to UnionQuery so you could do something like:
UnionQuery.union(q1, q2).addQueries(UnionQuery.Type.UNION_ALL, q3);
FYI, i've added this code to the trunk of SqlBuilder, it will be in the 2.0.5 release.
I've also added support for the "intersect" and "except" operators as well (all of which can be mixed together).