Clever Geek Handbook

Requires ANSI 92 recursive SQL statement

I have been translating SQL SQL SQL statements to their generic ANSI equivalent currently, and I am stuck with a recursive expression using a WITH statement.

To focus on the problem, I will simplify this problem as follows

If I have two tables

  • ReportingUnit

    • col1: key
    • col2: ParentReportingUnitKey

  • An object

    • col1: key
    • col2: ParentReportingUnitKey

This structure describes the reporting unit hierarchy up to the object, where a reporting unit can have 0 .. 1 direct parent reporting units and 0 .. n child reporting units.

An object is a "sheet" record that refers to a reporting unit.

I need to create a valid ANSI 92 SQL query (or, in the worst case, one that will work on Oracle, DB2 and SQL Server) that will return all objects related to a given reportable block anywhere in the hierarchy.

eg.

  • ReportingUnit R1 has ReportingUnit children R1.1 and R1.2
  • ReportingUnit R1.1 has children R1.1.1, R1.1.2

  • ReportingUnit R1.2 has children R1.2.1, R1.2.2

  • Facility F1 has a parent reporting module R1.1.1

  • Object F2 has a parent reporting unit R1.1.2
  • Object F3 has a parent reporting block R1.2.1
  • Object F4 has a parent reporting unit R1.2.2

Considering that the ReportingUnit table can have 0 .. n levels of recursion, how can I return all 4 objects from the SQL query given the ReportingUnit = R1 parameter?

+1


source to share


2 answers


I'm pretty sure there were no recursive statements in SQL-92; the earliest version where this was confirmed was SQL-99.

Hence you are not using SQL-92. Why do you think SQL-92 is desirable? Is this a baseline level of SQL functionality or is there some other reason?



Current versions of DB2 have a WITH clause and can execute recursive queries. I believe Oracle has a WITH clause; I'm not sure if he can use recursive queries. Oracle also has a completely non-standard and non-relational CONNECT BY PRIOR. I'm not sure what MS SQL Server supports.

There is a fairly strong chance that you will not be able to find one syntax that is supported by all three of the DBMSs you mentioned.

+3


source


There is no SQL-92 solution for recursive queries.

The best option is to use one of the hierarchical relationship coding solutions so that you can query all descendants or ancestors using standard SQL.



See the summary here: " What is the most efficient / elegant way to parse a flat table into a tree? "

Or read " Trees and Hierarchies in SQL for Smarties " by Joe Celko.

+2


source






More articles:


All Articles