Features are provided to allow the creation of a With query, With Recursive query or With Recursive view. To create one of these structures, a special With (Recursive) Query SQL element must be dragged onto the special With (Recursive) Clause on the expert options tab. The following preliminary steps are required:
- Build a separate Variable Creation analysis for each required seed query and recursive query.
- Check the With seed query or With recursive query option on the analysis parameters tab of each of these analyses.
- Set the output style of the seed query or recursive query analysis to not Store the tabular output of this analysis in the database for each of these analyses.
- Set the Output - storage option to Generate the SQL for this analysis but do not execute it in each of these analyses to embed the SQL in the containing analysis (not required for the seed query but necessary to place all of the SQL in one query).
- If a With Recursive View is being created, set the output storage option for the controlling or base analysis to create a view and remove all variables from its definition!
When the seed and recursive analyses have been created, a column from each of them can be dragged under the With (Recursive) Query element, which requires setting Input Source to Analysis. For more information, refer to Variable Creation - INPUT - Expert Options - SQL Elements.