Subqueries

You can execute subqueries using the Query.from(Query query, String alias) method. For example, the Java code shown below is equivalent to this SQL query:

SELECT a.id FROM (SELECT b.id FROM AllType b) AS a WHERE a.id = 1

Use this Java code:

Query query1 = new Query();
query1.select("b.id");
query1.from("AllType", "b");
Query query2 = new Query();
query2.select("a.id");
query2.from(query1, "a");
AttributeTest ts = new AttributeTest();
ts.setAttribute("a.id");
ts.setValue(1);
query2.where(ts);
com.sybase.persistence.QueryResultSet qs = SUP101DB.executeQuery(query2);

You can use a subquery as the selected item of a query. Use the SelectItem to set selected items directly. For example, the Java code shown below is equivalent to this SQL query:

SELECT (SELECT count(1) FROM AllType c WHERE c.id >= d.id) AS cn, id
FROM AllType d

Use this Java code:

Query selQuery = new Query();
selQuery.select("count(1)");
selQuery.from("AllType", "c");
AttributeTest ttt = new AttributeTest();
ttt.setAttribute("c.id");
ttt.setOperator(AttributeTest.GREATER_EQUAL);
Column cl = new Column();
cl.setAlias("d");
cl.setAttribute("id");
ttt.setValue(cl);
selQuery.where(ttt);

com.sybase.collections.GenericList<com.sybase.persistence.SelectItem> selectItems = new com.sybase.collections.GenericList<com.sybase.persistence.SelectItem>();
SelectItem item = new SelectItem();
item.setQuery(selQuery);
item.setAlias("cn");
selectItems.add(item);
item = new SelectItem();
item.setAttribute("id");
item.setAlias("d");
selectItems.add(item);
Query subQuery2 = new Query();
subQuery2.setSelectItems(selectItems);
subQuery2.from("AllType", "d");
com.sybase.persistence.QueryResultSet qs = SUP101DB.executeQuery(subQuery2);