A very clever approach to the problem, Mike. Though as you also noted
that it may not work unless the data follows certain rules - namely a
strict hierarchy that makes hierarchical queries sensible. In the OP's
case, the hierarchical queries will fail on the rectangle that
contains 2 triangles - since some nodes have multiple parents. A lot
probably depends on what data is allowed in representing the shape.
All the numbers representing the nodes look pretty arbitrary to me.
mike2322_at_hotmail.com (Mike Liu) wrote in message news:<2262aa8e.0211181210.2825e4a7_at_posting.google.com>...
> tonycantara123_at_hotmail.com (Tony Cantara) wrote in message news:<3d0c2563.0211161456.50f73e6c_at_posting.google.com>...
> > Hi, I have designed a table that contains the following information
> > about some simple shapes
> >
> > I need a bit of help writing some SQL (Oracle 8i/9i Enterprise). I
> > would like to query the database and count, for example, the number of
> > shapes with triangles in them. For all the data below this would
> > return 2 (1 for the shape that is a triangle and 1 for the shape that
> > is a rectangle made up of 2 triangles).
> >
> > If the design is bad, I would appreciate constructive critism on what
> > would be a better design - at this stage nothing is set in concrete
> > (initially I thought this would be an easy query to write using a
> > CONNECT BY PRIOR construct clause but this doesn't work for
> > 'cyclic/loop data' like that below).
> > Many thanks
> > Tony
> >
> >
> > CREATE TABLE tblshapes(shapeID NUMBER,node NUMBER,nextnode_linkedlist
> > NUMBER);
> >
> > -- RECTANGLE
> > -- 1---------2
> > -- | |
> > -- | |
> > -- | |
> > -- 4---------3
> > --
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (100,1,4);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (100,2,1);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (100,3,2);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (100,4,3);
> >
> >
> > -- TRIANGLE
> > -- 10
> > -- |\
> > -- | \
> > -- | \
> > -- | \
> > -- 11---12
> > --
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (200,10,12);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (200,11,10);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (200,12,11);
> >
> >
> > -- TWO TRIANGLES ADDED TOGETHER TO MAKE A RECTANGLE
> > -- 20----21
> > -- |\ |
> > -- | \ |
> > -- | \ |
> > -- | \|
> > -- 23----22
> > --
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,20,23);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,20,22);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,21,20);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,22,21);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,23,22);
>
> Tony,
>
> I'm not sure if this can be done is plain SQL. What I can think of is
> to write a stored function to check if there are any triangles in a
> shape. The function could be something like:
>
> create or replace function hasTriangle (s_id number)
> return varchar2
> as
> triangle_found exception;
> pragma exception_init(triangle_found, -1436);
> begin
> for rec in ( select *
> from (
> select * from tblshapes
> where shapeid = s_id )
> connect by prior nextnode_linkedlist = node
> and level <= 4 ) loop
> null;
> end loop;
> return 'N';
> exception
> when triangle_found then
> return 'Y';
> end;
>
> Then you could do
>
> select count(*)
> from (
> select distinct shapeid
> from tblshapes
> where hasTriangle(shapeid) = 'Y'
> )
>
> to count.
>
> The idea is that, for any shape, if you can go 4 levels deep in the
> "connect by" query without getting a loop, then you don't have
> triangles in this shape. But for this to work, you can't have data
> like
>
> insert into tblshapes values (100, 1, 2);
> insert into tblshapes values (100, 2, 3);
> insert into tblshapes values (100, 1, 3);
>
> HTH,
> Mike
Received on Tue Nov 19 2002 - 14:01:22 CST
