Re: Nested Coalescing possible in SQL?

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 4 Jun 2004 17:35:43 -0700
Message-ID: <18c7b3c2.0406041635.147a645b@posting.google.com>

>> if an entity instance (one row) does not specify a value for one of
its field [sic]s it should inherit the values from its nearest parent that does have a value for this field [sic]. <<

Rows are not records; fields are not columns; tables are not files. It drives me nuts to see people screw up the terms and therefore their mental model of how SQL works. Let's put this into a simplified nested set model which has more NULL-able columns than the payroll system of a major auto company:

CREATE TABLE Nodes
(node_id INTEGER NOT NULL PRIMARY KEY,
 col_1 CHAR(5),
 col_2 CHAR(5),
 ..);

CREATE TABLE Tree
(node_id INTEGER NOT NULL UNIQUE

         REFERENCES Nodes(node_id),
 lft INTEGER NOT NULL,
 rgt INTEGER NOT NULL,
 PRIMARY KEY (lft, rgt),
 << other tree constraints here >>
 ..);

Now update all the rows where any column has a NULL. You will have to run this update once for every level in the tree at most.

UPDATE Nodes

   SET col_1

• COALESCE(Nodes.col_1, -- keep non-nul (SELECT B.col_1 -- find parent FROM Tree AS E LEFT OUTER JOIN Tree AS B ON B.lft
  • (SELECT MAX(lft) FROM Tree AS S WHERE E.lft > S.lft AND E.lft < S.rgt)), col_2
• COALESCE(Nodes.col_2, (SELECT B.col_2 FROM ..)),

       Etc.
 WHERE (col_1 || col_2 || ..||col_n) IS NULL;

This probably scared you. The WHERE clause uses the fact that NULLs propagate; we don't care *which* column is NULL, so why look for useless details? That left outer join scalar subquery is how to get the immediate superiors (B= boss, E= employee) in a hierarchy. The COALESCE() will retain an existing non-NULL value. Received on Fri Jun 04 2004 - 19:35:43 CDT