# Recursive Queries and CTEs If you're an #OracleProfessional (like me) looking to transition to a #DataEngineer role, one of the key skills you'll need is #AdvancedSQL techniques like #RecursiveQueries and #CTEs. This is part of my presentation this week at @RMOUG and I thought I would share. ## Recursive Queries Recursive queries, also known as hierarchical queries, allow you to query hierarchical or tree-like data structures, where each record is related to other records in the same table. Recursive queries are defined as queries that reference themselves in the `FROM` clause, allowing them to navigate through the hierarchical structure of the data. Recursive queries are commonly used in organizational charts, file systems, and other structures with a hierarchical or parent-child relationship. They are particularly useful for traversing large or complex data structures, where other querying methods may be inefficient or impractical. ### Common Table Expressions (CTEs) In SQL, recursive queries can be defined using a Common Table Expression (CTE) that references itself. The CTE includes a base query that selects the root nodes of the hierarchy, followed by a recursive part that selects the child nodes and then recursively calls itself to select the child nodes of those child nodes. The recursion continues until all the nodes in the hierarchy have been selected. ### Syntax The syntax for a recursive query using a CTE is as follows: ``` SQL WITH RECURSIVE cte_name AS ( -- Base query SELECT ... FROM ... WHERE ... UNION ALL -- Recursive query SELECT ... FROM ... WHERE ... AND ... JOIN cte_name ON ... ) SELECT ... FROM cte_name WHERE ... ``` ## Example Here's an example of a recursive query that selects data from a `bom_components` table that has a parent-child relationship: ``` SQL WITH RECURSIVE bom_tree AS ( SELECT id, name, parent_id, quantity FROM bill_of_materials WHERE parent_id IS NULL UNION ALL SELECT b.id, b.name, b.parent_id, b.quantity * t.quantity FROM bom_components b JOIN bom_tree t ON b.parent_id = t.id ) SELECT name, SUM(quantity) AS total_quantity FROM bom_tree WHERE parent_id IS NULL GROUP BY name; ``` In this example, the CTE `bom_tree` selects the top-level components of the bill of materials (where `parent_id` is `NULL`), and then recursively selects child components by joining the `bom_components` table to the CTE and calculating the quantity of child components based on the quantity of their parent components. The final query then groups the components by name and calculates the total quantity required for each component. Sure, here's an example result set for the `bom_tree` recursive #SQLQuery: | name | total_qty | | ----------- | ---------:| | Product A | 25 | | Component 1 | 50 | | Component 2 | 25 | | Component 3 | 100 | This result set shows the bill of materials for a product, with the top-level component "Product A" at the root, and child components "Component 1" and "Component 2" branching off from it. The component "Component 1" has a child component "Component 3", which has a quantity of 2. ### Other Use Cases for Recursive Queries Recursive queries can be used in many different scenarios, such as: - Organizational charts: A company's organizational chart can be modeled using a recursive query, with each employee's record containing a reference to their manager's record. - File systems: A file system can be modeled using a recursive query, with each file or directory record containing a reference to its parent directory's record. - Bill of materials: A bill of materials for a product can be modeled using a recursive query, with each component's record containing a reference to the product's record that it belongs to. Recursive queries can also be used for many other applications where data has a hierarchical structure, such as nested comments on social media or product categories on e-commerce sites. ## Real Life Example for an Oracle Professional In Oracle EBS and Oracle Cloud Applications, the Bill of Materials (BOM) table is a key table that stores information about the components and sub-components that make up a finished product. It's a hierarchical table The BOM table is used in the manufacturing process (and supply chain) to ensure that the correct components are used in the production of each finished product, and to track the costs associated with producing each unit. There are several tables that are used to store Bill of Materials (BOM) data, including: - `BOM_BILL_OF_MATERIALS`: Stores information about the top-level bills of materials. - `BOM_INVENTORY_COMPONENTS`: Stores information about the components that make up each top-level item in the BOM. - `BOM_OPERATIONAL_ROUTINGS`: Stores information about the manufacturing operations that are required to produce each top-level item in the BOM. - `BOM_COMPONENTS`: Stores information about all components in the BOM, including sub-components and purchased items. - `BOM_ALTERNATE_DESIGNATORS`: Stores information about alternate part numbers or descriptions that can be used for each component in the BOM. - `BOM_DEPARTMENTS`: Stores information about the manufacturing departments that are associated with each manufacturing operation in the BOM. These tables can be used to query BOM data for a variety of purposes, such as determining the components that go into a finished product, calculating the cost of production, or analyzing manufacturing efficiency. ## Writing to Temporary Tables is so Old School It's interesting to note that in the earlier versions of Oracle Manufacturing, advanced SQL techniques such as Recursive Queries were not available. Instead, they had to write the data to a temporary table, which would then be queried. The temporary table used in the process is `MTL_BOM_EXPLOSIONS_TEMP`, and the process name is `CSTPMBOM`. We would run this process out-of-band then query the temp table, which is a real table that retains rows by the way. But, There's More Than One Way To Do It #TMTOWDI. ## An Exercise for your Data Engineering SQL Skills As a data engineer, an exercise that you could undertake to replace the temporary table process for the BOM explode function with a more efficient one using only recursive queries involves the following steps: 1. Create a SQL query that uses Recursive Queries to generate a list of all components and their respective quantities required for production. This query should start with the finished product and recursively explode each component to determine its sub-components and their respective quantities. 2. Be sure to include the information from the related tables, you can find the column names you need in `MTL_BOM_EXPLOSIONS_TMP` and figure out which tables those columns are in. 3. Refactor the existing BOM explode process to use the SQL query from step 1 instead of writing the data to a temporary table. 4. Test the new process to ensure that it generates the same results as the old process and that it is more efficient. 5. Refine the SQL query as necessary to improve performance and accuracy. By replacing the temporary table process with a more efficient one that uses Recursive Queries, you can eliminate the need for a temporary table and reduce the time and resources required to generate the BOM explosion. This exercise is a great way to develop your #SQLSkills skills in your transition from Oracle Professional to Data Engineer and gain a deeper understanding of how data is processed in manufacturing environments. ### Footnotes Keywords: Oracle Professional, Data Engineer, advanced SQL techniques, Recursive Queries, CTEs, hierarchical queries, tree-like data structures, Common Table Expressions, manufacturing, Bill of Materials, BOM tables, BOM explode process, temporary table, MTL_BOM_EXPLOSIONS_TEMP, CSTPMBOM, nested comments, social media, product categories, e-commerce sites, SQL query. Hashtags: #OracleProfessional #DataEngineer #AdvancedSQL #RecursiveQueries #CTEs #Manufacturing #BillofMaterials #SQLQuery #DataProcessing #SQLSkills.