Home → Active Query Builder → Analyzing SQL query structure → Enumeration of sub-queries in a complex query

5.3. Enumeration of sub-queries in a complex query

Below are the fragments of the QueryStructureDemo project that included in the installation package. You may review it for more details.

1. Enumeration of sub-queries in a query.

Use the QueryBuilder.SubQueries and the QueryBuilder.SubQueryCount properties to list all sub-queries in the query.

public void DumpSubQueriesInfo(StringBuilder stringBuilder, QueryBuilder queryBuilder)
{
	for (int i = 0; i < queryBuilder.SubQueryCount; i++)
	{
		if (stringBuilder.Length > 0)
		{
			stringBuilder.AppendLine();
		}

		DumpSubQueryInfo(stringBuilder, i, queryBuilder.SubQueries[i]);
	}
}

private void DumpSubQueryInfo(StringBuilder stringBuilder, int index, SubQuery subQuery)
{
    string sql = subQuery.GetResultSQL();

    stringBuilder.AppendLine(index.ToString() + ": " + sql);
}

2. Enmeration of union queries in a sub-query.

Union sub-query groups might be enlosed in parentheses, thus it's not a simple union sub-query list, but a list of single union sub-queries and union sub-query groups organized in a tree-like structure.

public void DumpQueryStructureInfo(StringBuilder stringBuilder, SubQuery subQuery)
{
    DumpUnionGroupInfo(stringBuilder, "", subQuery);
}
        
private void DumpUnionGroupInfo(StringBuilder stringBuilder, string indent, UnionGroup unionGroup)
{
	QueryBase[] children = GetUnionChildren(unionGroup);

	foreach (QueryBase child in children)
	{
		if (stringBuilder.Length > 0)
		{
			stringBuilder.AppendLine();
		}

		if (child is UnionSubQuery)
		{
			// UnionSubQuery is a leaf node of query structure.
			// It represent a single SELECT statement in the tree of unions
			DumpUnionSubQueryInfo(stringBuilder, indent, (UnionSubQuery) child);
		}
		else
		{
			// UnionGroup is a tree node containing one or more child nodes.
			// It represents a root of the subquery in the union tree or 
			// a group of sub-queries enclosed in parentheses in the union tree.
			unionGroup = (UnionGroup) child;

			stringBuilder.AppendLine(indent + unionGroup.UnionOperatorFull + "group: [");
			DumpUnionGroupInfo(stringBuilder, indent + "    ", unionGroup);
			stringBuilder.AppendLine(indent + "]");
		}
	}
}

private void DumpUnionSubQueryInfo(StringBuilder stringBuilder, string indent, UnionSubQuery unionSubQuery)
{
	string sql = unionSubQuery.GetResultSQL();

	stringBuilder.AppendLine(indent + unionSubQuery.UnionOperatorFull + ": " + sql);
}

private QueryBase[] GetUnionChildren(UnionGroup unionGroup)
{
	ArrayList result = new ArrayList();

	for (int i = 0; i < unionGroup.Count; i++)
	{
		result.Add(unionGroup[i]);
	}

	return (QueryBase[]) result.ToArray(typeof(QueryBase));
}