Search code examples
sqlrecursionhierarchical-dataansi-sqlansi-sql-92

ANSI 92 Recursive SQL Statement required

I am translating SQL Server SQL Statements into their ANSI generic equivalent at present, and am stuck with a recursive statement using a WITH statement.

For the sake of concentrating on the issue, I'll simplify the issue as follows

If I have two tables

  1. ReportingUnit

    • col1: Key
    • col2: ParentReportingUnitKey

  2. Facility

    • col1: Key
    • col2: ParentReportingUnitKey

This structure is describing a hierarchy of reporting units down to a facility, where a reporting unit may have 0 .. 1 direct parent reporting units and 0 .. n child reporting units.

A facility is a 'leaf' record, that links to a reporting unit.

I need to craft an ANSI 92 valid SQL Statement (or at worst one that will work on Oracle, DB2 and SQL Server) that will return all facilities related to a given reporting unit anywhere up the hierarchy.

e.g.

  • 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 unit R1.1.1

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

Bearing in mind there are may be 0 .. n levels of recursion in the ReportingUnit table, how can I return all 4 facilities from a SQL Statement given the parameter ReportingUnit=R1?

Solution

  • I'm tolerably certain that no recursive statements were available in SQL-92; the earliest version where that was supported was SQL-99.

    Consequently, you are stuck with not using SQL-92. Why do you think SQL-92 is desirable? Is it as a base level of SQL functionality, or is there some other reason?

    Current versions of DB2 have the WITH clause and can achieve recursive queries. I believe Oracle has the WITH clause too; I'm not sure whether it can achieve recursive queries using them. Oracle also has the wholly non-standard and non-relational CONNECT BY PRIOR. I'm not sure what MS SQL Server supports.

    There is a reasonably strong chance that you will be unable to find a single syntax that is supported by all three of your specified DBMS.