Query<\/strong><\/p>\n\n\n\nWITH -- starting data\n employee (ID, Name, IdManager) AS (\n VALUES ('I011', 'Employee_011', NULL) , ('I021', 'Employee_021', 'I011'),\n ('I022', 'Employee_022', 'I011'), ('I023', 'Employee_023', 'I011'),\n ('I031', 'Employee_031', 'I022'), ('I032', 'Employee_032', 'I022')\n,\n ('I041', 'Employee_041', 'I031'), ('I042', 'Employee_042', 'I031'),\n ('I043', 'Employee_043', 'I031'), ('I045', 'Employee_045', 'I032'),\n ('I046', 'Employee_046', 'I032'), ('I047', 'Employee_047', 'I032')\n,\n ('I051', 'Employee_051', 'I042'), ('I052', 'Employee_052', 'I046'),\n ('I061', 'Employee_061', 'I051')\n, ('I062', 'Employee_062', 'I051'),\n ('I063', 'Employee_063', 'I051'), ('I064', 'Employee_064', 'I052')\n , ('I065', 'Employee_065', 'I052'), ('I066', 'Employee_066', 'I052'))\n , hierarchy (ID, Name, Level) AS (\n SELECT ID, Name\n , 0 AS Level\n FROM employee\n WHERE IdManager IS NULL\n UNION ALL\n SELECT e.ID, e.Name, h.Level + 1\n FROM employee e\n INNER JOIN hierarchy h ON e.IDManager = h.ID\n )\nSELECT * FROM hierarchy;\n<\/pre>\n\n\n\nResult<\/strong><\/p>\n\n\n\nResult table (hierarchy)<\/strong><\/p>\n\n\n\n\n
\n
ID<\/td> NAME<\/td> LEVEL<\/td><\/tr> I011<\/td> Employee_011<\/td> 0<\/td><\/tr> I021<\/td> Employee_021<\/td> 1<\/td><\/tr> I022<\/td> Employee_022<\/td> 1<\/td><\/tr> I023<\/td> Employee_023<\/td> 1<\/td><\/tr> I031<\/td> Employee_031<\/td> 2<\/td><\/tr> I032<\/td> Employee_032<\/td> 2<\/td><\/tr> I041<\/td> Employee_041<\/td> 3<\/td><\/tr> I042<\/td> Employee_042<\/td> 3<\/td><\/tr> I043<\/td> Employee_043<\/td> 3<\/td><\/tr> I045<\/td> Employee_045<\/td> 3<\/td><\/tr> I046<\/td> Employee_046<\/td> 3<\/td><\/tr> I047<\/td> Employee_047<\/td> 3<\/td><\/tr> I051<\/td> Employee_051<\/td> 4<\/td><\/tr> I052<\/td> Employee_052<\/td> 4<\/td><\/tr> I061<\/td> Employee_061<\/td> 5<\/td><\/tr> I062<\/td> Employee_062<\/td> 5<\/td><\/tr> I063<\/td> Employee_063<\/td> 5<\/td><\/tr> I064<\/td> Employee_064<\/td> 5<\/td><\/tr> I065<\/td> Employee_065<\/td> 5<\/td><\/tr> I066<\/td> Employee_066<\/td> 5<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n\n\n\n<\/div>\n<\/div>\n\n\n\n
Detailed description of the query<\/strong><\/p>\n\n\n\nDefinition of the Employee Table:<\/strong><\/p>\n\n\n\n\nThe employee<\/strong> CTE is defined as a temporary table containing three columns: ID<\/strong> (employee identifier), Name<\/strong> (employee name), and IdManager<\/strong> (identifier of each employee’s manager).The table is populated with a series of values \u200b\u200bthat represent a company organizational structure. For example, ‘I011’ is an employee with no manager (NULL<\/strong>), which implies that he could be a manager or a department head (top Manager).<\/li>\n<\/ul>\n\n\n\nHierarchy construction:<\/strong><\/p>\n\n\n\n\nThe CTE hierarchy<\/strong> is a recursive CTE composed of two parts: a base part and a recursive part, separated by the UNION ALL<\/strong> operator.Basic Part<\/strong>: Select employees who do not have a manager (IdManager IS NULL<\/strong>). These employees represent the starting point of the hierarchy (for example, company management). These are assigned a Level<\/strong> 0, indicating the highest level of the hierarchy.Recursive Part<\/strong>: Connects each employee to their manager through an INNER<\/strong> JOIN<\/strong> between the CTE employee<\/strong> and the hierarchy<\/strong> itself. At each step of the recursion, the level increases by 1 (h.Level + 1<\/strong>), thus representing each employee’s position in the company hierarchy.<\/li>\n<\/ul>\n\n\n\nFinal Selection:<\/strong><\/p>\n\n\n\n\nThe final query SELECT * FROM hierarchy<\/strong>; selects all records from the CTE hierarchy<\/strong>, resulting in a list of employees with their respective levels in the organizational structure.<\/li>\n<\/ul>\n\n\n\nConsideration<\/strong><\/p>\n\n\n\n\nRecursive CTE is an effective way to handle hierarchical or recursive data in SQL, especially when the data structure is not flat but nested or multi-level. This type of query can be particularly useful in corporate environments where the organizational structure is complex. This type of query can be particularly useful in corporate environments where the organizational structure is complex.<\/li>\n<\/ul>\n\n\n\nPlease note that actual implementation and functionality may vary depending on the version of the iSeries operating system and DB2 database. It is always good practice to refer to the official IBM documentation for more precise and detailed information<\/p>\n","protected":false},"excerpt":{"rendered":"
Common Table Expressions (CTE) in SQL on IBM iSeries (AS\/400) are a powerful and flexible tool for creating temporary, reusable queries, simplifying and improving the readability of complex SQL queries. Below are the articles in the CTE series (including this…<\/p>\n","protected":false},"author":3,"featured_media":4183,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_kadence_starter_templates_imported_post":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","footnotes":""},"categories":[143,141],"tags":[160,162,46],"class_list":["post-4270","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-more-in-depth","category-manage-as400-with-sql","tag-cte-common-table-expression-en","tag-recursive-cte","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4270","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/comments?post=4270"}],"version-history":[{"count":3,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4270\/revisions"}],"predecessor-version":[{"id":4386,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4270\/revisions\/4386"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/media\/4183"}],"wp:attachment":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/media?parent=4270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/categories?post=4270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/tags?post=4270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/www.skillup.cloud\/wp-content\/uploads\/2024\/01\/employee_manager.png\")
<\/figure><\/div>\n\n\n