Home → Active Query Builder → Programmatic modification of SQL queries → How to add/check ROWNUM condition in Oracle query

7.11. How to add/check ROWNUM condition in Oracle query

Use the following function to add new or to modify existing ROWNUM condition for the Oracle query:

public void SetOracelRownum(int rowCount)
{
	// set ROWNUM for the currently selected sub-query
	UnionSubQuery usq = queryBuilder1.ActiveSubQuery.ActiveUnionSubquery;

	// or set ROWNUM for the main query
	UnionSubQuery usq = queryBuilder1.Query.FirstSelect;

	ArrayList ciList = new ArrayList();
		
	CriteriaItem rownumItem = null;

	// Check the query already contains ROWNUM criteria item
	for (int i = 0; i < usq.CriteriaList.Count; i++)
	{
		if (usq.CriteriaList[i].ExpressionString.Equals("ROWNUM", StringComparison.CurrentCultureIgnoreCase))
		{
			rownumItem = usq.CriteriaList[i];
			break;
		}
	}

	if (rownumItem == null)
	{
		// Add new ROWNUM criteria item
		rownumItem = usq.CriteriaList.AddExpression("ROWNUM");
	}

	// Clear previously set rownum conditions
	for (int i = 0; i < usq.CriteriaList.GetMaxConditionCount(); i++)
	{
		rownumItem.ConditionStrings[i] = "";
	}

	if (usq.CriteriaList.GetMaxConditionCount() == 0)
	{
		// Query contains no other conditions.
		// Just set the new rownum condition.
		rownumItem.ConditionStrings[0] = "< " + rowCount;
	}
	else
	{
		// Query contains other conditions.
		// Add rownum condition to every OR condition in the query.

		for (int i = 0; i < usq.CriteriaList.GetMaxConditionCount(); i++)
		{
			// CollectCriteriaItemsWithWhereCondition:
			// This function returns the list of conditions that were found in
			// the i-th criteria column, applied to specific clause (WHERE or HAVING).
			// Thus, this function collects all conditions joined with AND
			// within one OR operator.
			CollectCriteriaItemsWithWhereCondition(usq.CriteriaList, i, ciList);

			ciList.Remove(rownumItem);
		
			if (ciList.Count > 0)
			{
				rownumItem.ConditionStrings[i] = "< " + rowCount;
			}
		}
	}
}

private void CollectCriteriaItemsWithWhereCondition(CriteriaList criteriaList, int columnIndex, ArrayList result)
{
	result.Clear();

	for (int i = 0; i < criteriaList.Count; i++)
	{
		if (criteriaList[i].ConditionType == ConditionType.Where &&
			criteriaList[i].ConditionCount > columnIndex &&
			criteriaList[i].GetASTCondition(columnIndex) != null)
		{
			result.Add(criteriaList[i]);
		}
	}
}