[TORQUE-322] Support for SQL Set Operations (UNION, INTERSECT, ...)

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0
• Fix Version/s: 5.0
• Assignee: Thomas Fox
• Reporter: Michael Wa
• Created: 30/Jul/14 07:43
• Updated: 16/Sep/14 21:05
• Resolved: 16/Sep/14 21:05

Description

• It would be nice to support sql constructs like:
  SELECT * FROM sales2005 UNION SELECT * FROM sales2006;

• If you want to realize the UNION operator you have to do something like that:

[...]
Criteria criteriaA = new Criteria();
criteriaA.where( ... );
criteriaA.addSelectColumn(COLUMNX);

Query queryA = SqlBuilder.buildQuery(criteriaA);
String sqlA = queryA.toString();
List<Object> replacementsA = queryA.getPreparedStatementReplacements();

Criteria criteriaB = new Criteria();
criteriaB.where( ... );
criteriaB.addSelectColumn(...);

Query queryB = SqlBuilder.buildQuery(criteriaB);
String sqlB = queryB.toString();
List<Object> replacementsB = queryB.getPreparedStatementReplacements();

List<Object> replacements = Lists.newArrayList();
replacements.addAll(replacementsA);
replacements.addAll(replacementsB);

String sql = COLUMNX + " IN (" + sqlA + " UNION " + sqlB + ")";

mainCriteria.whereVerbatimSql(sql, replacements.toArray());
[...]