SQL generated by the query--recursive queries - Documentation for AR System 21.02

This section shows the SQL generated by the preceding recursive query. Different databases generate different SQL.

Note

In these examples, an AR System administrator made the API call, and the form did not use row-level security. If a nonadministrator makes the API call and the form uses row-level security, the SQL generated by the call will differ from these examples.

Microsoft SQL Server

The SQL generated by Microsoft SQL Server databases uses the Common Table Expressions (CTE) implementation:

; WITH T170_3244 (C536870913, C536870914, C536870915, LEVEL) AS
(	SELECT J0.C536870913, J0.C536870914, J0.C536870915, 1	FROM T170 J0	WHERE (J0.C536870913 = N'Bob Jones')	UNION ALL	SELECT J0.C536870913, J0.C536870914, J0.C536870915, J1.LEVEL+1	FROM T170 J0, T170_3244 J1	WHERE (J0.C536870915 = J1.C536870914) AND (J1.LEVEL < 3)
)
SELECT J0.C536870913, J0.C536870914, J0.C536870915
FROM T170_3244 J0

Oracle

The SQL generated by Oracle databases uses the native Oracle implementation:

SELECT J0.C536870913 a0, J0.C536870914 a1, J0.C536870915 a2 FROM
(	SELECT J0.C536870913, J0.C536870914, J0.C536870915	FROM T2672 J0	START WITH (J0.C536870913 = 'Bob Jones')	CONNECT BY (J0.C536870915 = PRIOR J0.C536870914 AND LEVEL <= 3)
) J0