Re: Nested Coalescing possible in SQL?

From: Jan <janik_at_pobox.sk>
Date: 4 Jun 2004 02:50:51 -0700
Message-ID: <81511301.0406040150.65d90554@posting.google.com>

e.g. in Oracle 9i,

you can:

Creating demo data

CREATE TABLE T
(
  CHILD_ID NUMBER,
  PARENT_ID NUMBER,

  ATTR1      VARCHAR2(10),
  ATTR2      VARCHAR2(10)

)

INSERT INTO T VALUES (1 NULL,'A','000'); 
INSERT INTO T VALUES (2,1, NULL, '111'); 
INSERT INTO T VALUES (3,1, 'B', NULL); 
INSERT INTO T VALUES (4,2, NULL, NULL); 
INSERT INTO T VALUES (5, 2, 'C', '999'); 
INSERT INTO T VALUES (6, 5, NULL, NULL); 

and the query is (it is too ugly with INSTR/SUBSTR, but maybe faster then an inline query per each attribute with another CONNECT BY):

---

SELECT child_id,PARENT_ID,attr1,attr2,tree_path,

       RTRIM(SUBSTR(all_attr1,INSTR(all_attr1,'/',-1,2)+1),'/') inherit_attr1,

           RTRIM(SUBSTR(all_attr2,INSTR(all_attr2,'/',-1,2)+1),'/') inherit_attr2
FROM (SELECT child_id,parent_id,attr1,attr2,

       SYS_CONNECT_BY_PATH(TO_CHAR(child_id), '/') tree_path,	   
	   CASE 
	       WHEN attr1 IS NOT NULL THEN '/'||attr1||'/'
		   ELSE REPLACE('/'||SYS_CONNECT_BY_PATH(attr1,'/'),'//','/')      
	   END	   all_attr1,
	   
	   CASE 
	       WHEN attr2 IS NOT NULL THEN '/'||attr2||'/'
		   ELSE REPLACE('/'||SYS_CONNECT_BY_PATH(attr2,'/'),'//','/')      
	   END	   all_attr2
	   
   FROM T
     START WITH parent_id IS NULL
     CONNECT BY parent_id=PRIOR child_id) v
--------------------------------------------------------

jlanfield2003_at_yahoo.com (Jeff Lanfield) wrote in message news:<235c483f.0406011716.37d00399_at_posting.google.com>...
> First of all, I apologize if coalescing is not the right term to
> describe my problem. I have a tree where each node has the same set of
> attributes (is the same entity) but child nodes should inherit
> attribute values from parent node.
>
> for example, say I have the following table:
>
> (nodeId int , color varchar, phone varchar) with two rows
>
> 5, "GREEN", "555-1212"
> 7, NULL, "777-5555"
> 8, NULL, NULL
> 9, "BLUE", NULL
>
> in addition there is a tree structure that specifies that node 5 is
> the parent of node 7, and 7 is the parent of nodes 8 and 9. I know
> there is many ways to make trees in SQL but as a simple example let's
> say the tree is:
>
> id, parentid
> 8, 7
> 9, 7
> 7, 5
>
> Thus in this case, node 7 inherits the value "GREEN" from node 5 for
> attribute "color", but provides its own value "777-5555" for attribute
> "phone". Node 8, in turn, inherits "GREEN" for "color" from node 7
> (really from node 5 since 7 did not specify its own) and "777-5555"
> for "phone" from node 7. Node 9 provides its own value for "color" and
> inherits the one for "phone" from Node 7.
>
> So the runtime values in the application are:
>
> Node 5: "GREEN", "555-1212"
> Node 7: "GREEN", "777-5555"
> Node 8: "GREEN", "777-5555"
> Node 9: "BLUE", "777-5555"
>
> Question 1: Is there a single SQL statement that for a given node can
> replace the NULLs with inherited values from the parent node?
>
> Question 2: Is there a better way to structure such data in SQL as to
> make answer to question 1 possible?
>
> I would restate the problem as follows:
>
> In a nested structure child nodes inherit values from parent nodes _by
> reference_ or specify their own. "By reference" is the key word here.
> If it wasn't for that you could just duplicate the necessary values
> from the parent entitity upon creation.
>
> Thanks!
>
> - Jeff
Received on Fri Jun 04 2004 - 04:50:51 CDT