> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Build Iterative Queries with Data Flows

> Learn how to use Ocient data flows to execute iterative computations such as accumulation patterns, graph traversals, and convergence algorithms.

export const Ocient = "Ocient®";

The examples demonstrate how to use {Ocient} data flows to execute iterative computations within the database. You use `WHILE` and `FOR` loops to implement patterns such as accumulation, graph traversal, and convergence algorithms.

Data flows provide loop constructs that are especially useful when a computation requires repeated passes over data, such as propagating values through a graph or refining an estimate until it stabilizes. The `RETURN SELECT` statement in a query data flow returns the final result set to the caller.

For details about data flow syntax and supported statements, see [Data Flows](/data-flows).

## Prerequisites

Before you begin, ensure that you have:

* A running Ocient deployment with access to execute SQL statements.
* The `CREATE TABLE` and `INSERT` privileges on the target schema.

## Accumulation with a `WHILE` Loop Example

This example computes a running factorial using a `WHILE` loop. The example demonstrates the basic loop pattern with variable manipulation and a safety limit.

<Steps>
  <Step title="Execute the Query Data Flow">
    Execute this query data flow to compute the factorial of 10. The `MAX ITERATIONS` clause acts as a safety limit to prevent infinite loops if the loop never satisfies the termination condition.

    ```sql SQL theme={null}
    BEGIN QUERY DATAFLOW
        DECLARE @n BIGINT = 10;
        DECLARE @i BIGINT = 1;
        DECLARE @result BIGINT = 1;

        WHILE (@i <= @n) MAX ITERATIONS 100 DO
            SET @result = @result * @i;
            SET @i = @i + 1;
        END WHILE;

        RETURN SELECT @result AS factorial;
    END DATAFLOW;
    ```

    This data flow returns a single row containing the factorial of 10 (3628800).
  </Step>
</Steps>

## Graph Traversal with Iterative Expansion Example

This example finds all nodes reachable from a starting node in a directed graph by iteratively expanding the frontier of discovered nodes. Each iteration discovers new neighbors and adds them to the set of reached nodes.

<Steps>
  <Step title="Create the Graph Table">
    Create a table representing a directed graph with edges between nodes.

    ```sql SQL theme={null}
    CREATE TABLE edges (
        src INT,
        dst INT
    );
    ```
  </Step>

  <Step title="Insert Sample Data">
    Insert edges representing a simple directed graph.

    ```sql SQL theme={null}
    INSERT INTO edges VALUES
        (1, 2), (1, 3), (2, 4), (3, 4),
        (4, 5), (5, 6), (6, 7), (7, 8),
        (10, 11), (11, 12);
    ```
  </Step>

  <Step title="Execute the Traversal Data Flow">
    Execute this query data flow to find all nodes reachable from node 1.

    ```sql SQL theme={null}
    BEGIN QUERY DATAFLOW
        DECLARE @start_node INT = 1;
        DECLARE @prev_count BIGINT = 0;
        DECLARE @curr_count BIGINT = 0;

        -- Initialize the reached set with the starting node
        CREATE TABLE #reached (node_id INT);
        INSERT INTO #reached VALUES (@start_node);

        -- Iteratively expand the frontier
        WHILE (true) MAX ITERATIONS 100 DO
            -- Add newly discovered neighbors
            INSERT INTO #reached
                SELECT DISTINCT e.dst
                FROM edges e
                INNER JOIN #reached r ON e.src = r.node_id
                WHERE e.dst NOT IN (SELECT node_id FROM #reached);

            -- Check if the set grew
            SET @curr_count = (SELECT COUNT(*) FROM #reached);
            IF (@curr_count = @prev_count) THEN
                BREAK;
            END IF;
            SET @prev_count = @curr_count;
        END WHILE;

        RETURN SELECT node_id FROM #reached ORDER BY node_id;
    END DATAFLOW;
    ```

    This data flow returns all nodes reachable from node 1: 1, 2, 3, 4, 5, 6, 7, and 8. Nodes 10, 11, and 12 are not reachable from node 1 and do not appear in the results.
  </Step>
</Steps>

## Convergence Algorithm Example

This example implements an iterative averaging algorithm that converges to a stable value. Each iteration computes the average of the current value and a target, stopping when the change between iterations falls below a threshold.

<Steps>
  <Step title="Execute the Convergence Data Flow">
    Execute this query data flow to iterate until the value converges.

    ```sql SQL theme={null}
    BEGIN QUERY DATAFLOW
        DECLARE @value DOUBLE = 100.0;
        DECLARE @target DOUBLE = 42.0;
        DECLARE @threshold DOUBLE = 0.001;
        DECLARE @prev_value DOUBLE = 0.0;
        DECLARE @iterations BIGINT = 0;

        WHILE (true) MAX ITERATIONS 1000 DO
            SET @prev_value = @value;
            SET @value = (@value + @target) / 2.0;
            SET @iterations = @iterations + 1;

            IF ABS(@value - @prev_value) < @threshold THEN
                BREAK;
            END IF;
        END WHILE;

        RETURN SELECT @value AS converged_value, @iterations AS iterations;
    END DATAFLOW;
    ```

    Output: `42.0009, 16` (converged value and iteration count)
  </Step>
</Steps>

## Iterator-Style FOR Loop Example

This example uses an iterator-style `FOR` loop to iterate over query results and accumulate a sum.

<Steps>
  <Step title="Execute the Iterator Data Flow">
    Execute this query data flow to sum values from a query result.

    ```sql SQL theme={null}
    BEGIN QUERY DATAFLOW
        DECLARE @val BIGINT;
        DECLARE @total BIGINT = 0;

        FOR @val IN (SELECT c1 FROM sys.dummy10) DO
            SET @total = @total + @val;
        END FOR;

        RETURN SELECT @total AS row_sum;
    END DATAFLOW;
    ```

    Output: `55`

    The iterator-style `FOR` loop executes once per row returned by the `SELECT` statement. The source query must return exactly one column.
  </Step>
</Steps>

## Related Links

[Data Flows](/data-flows)

[Stage and Transform Data with Data Flows](/stage-and-transform-data-with-data-flows)

[Validate Data Before Loading with Data Flows](/validate-data-before-loading-with-data-flows)

[Data Query Language (DQL) Statement Reference](/data-query-language-dql-statement-reference)