HomeActive Query Builder - Query analysis and modificationProgrammatic modification of SQL queriesHow to add/check ROWNUM condition in Oracle query

2.12. 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]);
}
}
}

This page was: Helpful | Not Helpful