Compound Query
A compound query is a SQL statement that combines more than one component query into a single SQL statement via a set operator. Set operators include UNION, UNION ALL, INTERSECT, and MINUS. A compound query is composed by specifying a set operator and adding component queries. The component queries are StatementSpec objects so nesting of compound queries is also supported.
The following example builds a compound query to return a specific PartMaster number and the numbers of all of its alternates. Note that only numbers are selected, not full objects. This is necessary because, if all subclasses are considered, the compound query statement must include all subclass tables. These subclass tables may contain additional columns that would make the select list for each statement incompatible with other component statements. SQL requires that each component query in a compound statement must have the same number and corresponding type in the select list. Following is the SQL for this query:
SELECT A0.number
FROM WTPartMaster A0
WHERE (A0.name = ‘ENGINE')
UNION
SELECT A2.number
FROM WTPartMaster A0,WTPartAlternateLink A1,WTPartMaster A2
WHERE (A0.name = ‘ENGINE') AND
(A0.idA2A2 = A1.idA3A5) AND (A2.idA2A2 = A1.idA3B5)
The following code constructs the query specification. The first select constructed is for PartMasters with the name ENGINE.
QuerySpec partSelect = new QuerySpec();
int partIndex = partSelect.appendClassList(wt.part.WTPartMaster.class, false);
partSelect.appendWhere(new SearchCondition(wt.part.WTPartMaster.class,
WTPartMaster.NAME, SearchCondition.EQUAL, "ENGINE"), new int[]
{ partIndex });
The next select is constructed for returning PartMaster alternates. An alternate is represented by the WTPartAlternateLink class, which is a many-to-many association between PartMasters. A join must be specified across this association from the original part to its alternates.
QuerySpec altSelect = new QuerySpec();
partIndex = altSelect.appendClassList(wt.part.WTPartMaster.class, false);
int altIndex = altSelect.appendClassList(W wt.part.WTPartAlternateLink.class,
false);
int altPartIndex = altSelect.appendClassList(wt.part.WTPartMaster.class,
false);
altSelect.appendSelect(new ClassAttribute(
wt.part.WTPartMaster.class, wt.part.WTPartMaster.NUMBER),
new int[] { altPartIndex }, false);
altSelect.appendWhere(new
SearchCondition(wt.part.WTPartMaster.class,
WTPartMaster.NAME, SearchCondition.EQUAL, "ENGINE"), new int[]
{ partIndex });
altSelect.appendJoin(altIndex, wt.part.WTPartAlternateLink.ALTERNATES_ROLE,
partIndex);
altSelect.appendJoin(altIndex, wt.part.WTPartAlternateLink.ALTERNATE_FOR_ROLE,
altPartIndex);
Finally, the compound statement is constructed using the two previous queries and the UNION set operator.
CompoundQuerySpec compound = new CompoundQuerySpec();
compound.setSetOperator(SetOperator.UNION);
compound.addComponent(partSelect);
compound.addComponent(altSelect);