News
Abstract
A second recursive query technique called a hierarchical query has been added to Db2 i. This technique is a more concise method of representing a recursive query.
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > CONNECT BY
Article: Hierarchical Queries with DB2 Connect By
IBM i Knowledge Center supporting material:
For example, return the organizational structure of the DEPARTMENT table. Use the level of the department to visualize the hierarchy.
CALL QSYS.CREATE_SQL_SAMPLE('MYDB');
SET CURRENT SCHEMA MYDB;
SET CURRENT PATH MYDB;
SET CURRENT SCHEMA MYDB;
SET CURRENT PATH MYDB;
SELECT LEVEL,
CAST(SPACE((LEVEL - 1) * 4) || '/' || DEPTNAME AS VARCHAR(40)) AS DEPTNAME
FROM DEPARTMENT
START WITH DEPTNO = 'A00'
CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT
Returns:
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
15 January 2020
UID
ibm11167706