SQL generated by the query--recursive queries

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

Important

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.

Related topic

Retrieving-entries-from-multiple-forms

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

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*