I have this query that works as expected. Within Oracle SQL Developer it returns ~19,500 rows. Unfortunately our current application limits our result to 10,000. I know I will need to run this query multiple times and merge the resultsets. But I need to be able to limit query results in blocks of 10,000.
Adding a Where clause before the Start statement only works for the first execution:
Where RowNum Between 1 and 10001 works as expected.
Where RowNum Between 10001 and 20001 returns no rows.
Based on my testing, limiting by Level will not work due to the number of number of children within each level.
Thanks for all your help!
Select WFS.WORKFLOWSTEPID , WFS.STEPTYPE , WFS.SUBWORKFLOWBASEID , WFS.SUBWORKFLOWID , WFS.Sequence , WFS.WORKFLOWSTEPNAME , WFS.Sequence ParentSEQ , WFB.WorkflowBaseID , WFB.WORKFLOWNAME , WFS.WORKFLOWID , Level from WorkflowBase WFB Inner Join Workflow WF on WFB.REVOFRCDID = WF.Workflowid Inner Join WorkflowStep WFS on WF.WORKFLOWID = WFS.WorkflowID Start With WFS.WORKFLOWID = '48810e80000001ca' Connect by Prior WFS.SubWorkflowBaseID = WFB.WorkflowBaseID Order Siblings by ParentSeq, Sequence