#21 Create embrace operator

[Riccardo Prandini]

Sometimes is very important surround a sql statement between braces an example in DB2 could be

select union select

if you use

select fetch first union select fitch first DB2 complains about wrong syntax.

if you do

(select fetch) union (select fetch) it's OK
you can also do
(select fetch) union (select fetch) fetch

Because each DB could have a different behaviou, having an embrace instruction
public Query embrace (Query) can give the correct flexibility to statements generations

[Riccardo Prandini]

In this specific case it could be achived via Subquery operator

[Riccardo Prandini]

Another idea is the possibility that a a query has a flag embraced, if flagged the result query is outputted between braces.

[James Ahlborn]

I think the Subquery object provides the functionality you are looking for (as you found).

[Riccardo Prandini]

OK for examplpe how to use it correctly Subquery generate a SubQuery object, if I need to use it inside a union.

I have
SelectQuery q1=method_that_return_a_query_obj("p1")
SelectQuery q2=method_that_return_a_query_obj("p2")

UnionQuery unionQuery = UnionQuery.unionAll(q1, q2);

If I transform q1,q2 in Subquery obj I can't use them in UnionQuery.

Any Idea??

[James Ahlborn]

new UnionQuery(UnionQuery.Type.UNION_ALL)
    .addQueries(new Subquery(q1), new Subquery(q2));

[Riccardo Prandini]

Ok this is a good option

It has a side effect

• I need to encapsulate the Union with two brakets and add a Fetch Firs realized via

query.addCustomization(new DB2FetchFirstClause(limit));

The first task could be accomplished via

Subquery sub = new Subquery(unionQuery); seems a good idea......BUT

I need a specific OBJ for addCustomization and the method also return a Query object if I encapsulete query inside a Subquery() type can't be casted.

[James Ahlborn]

I'm sorry, i'm not following. could you provide a little more detail about what you are trying to accomplish and what isn't working?

[Riccardo Prandini]

I need to write

(
(SELECT x,y FROM tableA FETCH FIRST 200 ROWS ONLY) --q1
UNION
(SELECT u,v FROM tableB FETCH FIRST 200 ROWS ONLY) --q2
)FETCH FIRST 200 ROWS ONLY

I can write the internal FETCH FIRST via query.addCustomization(new DB2FetchFirstClause(limit));
SELECT x,y FROM tableA--> SELECT x,y FROM tableA FETCH FIRST 200 ROWS ONLY
SELECT u,v FROM tableB --> SELECT u,v FROM tableB FETCH FIRST 200 ROWS ONLY

I can ADD parenthesis via new Subquery(q1) new Subquery(q2) new Subquery(unionQuery)

new UnionQuery(UnionQuery.Type.UNION_ALL).addQueries(new Subquery(q1), new Subquery(q2));

new Subquery(new UnionQuery(UnionQuery.Type.UNION_ALL).addQueries(new Subquery(q1), new Subquery(q2)));

How to add the final fetch first to new Subquery(unionQuery)

[James Ahlborn]

Hmm, yeah, that's a gnarly problem. A Subquery isn't a customizable query object because it is an Expression, not a Query. i guess you would need a query "wrapper" of some sort which is a Query and customizable... hmmm.