Subqueries

You can execute subqueries using the Query.from(Query query, String alias) method. For example, the C# .NET 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 C# .NET 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.Attribute = "a.id";
ts.Value = 1;
query2.Where(ts);
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 C# .NET 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 C# .NET code:

Query selQuery = new Query();
selQuery.Select("count(1)");
selQuery.From("AllType", "c");
AttributeTest ttt = new AttributeTest();
ttt.Attribute = "c.id";
ttt.Operator = AttributeTest.GREATER_EQUAL;
Column cl = new Column();
cl.Alias = "d";
cl.Attribute = "id";
ttt.Value = cl;
selQuery.Where(ttt);

Sybase.Collections.GenericList<Sybase.Persistence.SelectItem> selectItems = new Sybase.Collections.GenericList<Sybase.Persistence.SelectItem>();
SelectItem item = new SelectItem();
item.Query = selQuery;
item.AsAlias = "cn";
selectItems.Add(item);
item = new SelectItem();
item.Attribute = "id";
item.Alias = "d";
selectItems.Add(item);
Query subQuery2 = new Query();
subQuery2.SelectItems = selectItems;
subQuery2.From("AllType", "d");
Sybase.Persistence.QueryResultSet qs = SUP101DB.ExecuteQuery(subQuery2);