Re: Hierarchical Queries--Help!!!
Hi Ashi,
I am quoting a solution using a stored procedure reviewed by Jonathan
Leffler:
CREATE TABLE keywordclass
(
Id INTEGER NOT NULL,
KC CHAR(1) NOT NULL CHECK (KC IN ('C', 'K')),
# KC => keyword (terminal) or class (non-terminal)?
Name CHAR(15) NOT NULL,
Parent INTEGER NOT NULL # Refers to Id
);
INSERT INTO KeyWordClass VALUES( 1, 'C', 'Living', 1);
INSERT INTO KeyWordClass VALUES( 2, 'C', 'Animal', 1);
INSERT INTO KeyWordClass VALUES( 3, 'C', 'Plant', 1);
INSERT INTO KeyWordClass VALUES( 4, 'K', 'Tiger', 2);
INSERT INTO KeyWordClass VALUES( 5, 'C', 'Bird', 2);
INSERT INTO KeyWordClass VALUES( 6, 'K', 'Parrot', 5);
CREATE PROCEDURE exp_keywordclass(start_here INTEGER, level INTEGER
DEFAULT 0)
RETURNING INTEGER, CHAR(1), CHAR(15), INTEGER, INTEGER;
DEFINE m_id INTEGER;
DEFINE m_kc CHAR(1);
DEFINE m_name CHAR(15);
DEFINE m_parent INTEGER;
DEFINE m_lev INTEGER;
IF level = 0 THEN
SELECT id, kc, name, parent
INTO m_id, m_kc, m_name, m_parent
FROM keywordclass
WHERE id = start_here; RETURN m_id, m_kc, m_name, m_parent, level WITH RESUME;
END IF
FOREACH WITH HOLD
SELECT id, kc, name, parent
INTO m_id, m_kc, m_name, m_parent
FROM keywordclass
WHERE parent = start_here
RETURN m_id, m_kc, m_name, m_parent, level + 1 WITH RESUME;
FOREACH EXECUTE PROCEDURE exp_keywordclass(m_id, level + 1)
INTO m_id, m_kc, m_name, m_parent, m_lev
RETURN m_id, m_kc, m_name, m_parent, m_lev WITH RESUME;
END FOREACH;
END FOREACH;
END PROCEDURE;
EXECUTE PROCEDURE exp_keywordclass(2);
Ashi Chaturvedula wrote:
>
> hi,
>
> Does Informix7.1 support hierarchical queries (similar to Oracle's
> connect by clause)? For eg. I have a table that has records in it which
> have parent/child/grandchild... upto n relationships stored. I need a
> way to query the records and display it with appropriate indents.
>
> Any help is appreciated, thanks in advance
>
> ashi@thomtech.com
> Thomson Technology Consulting Group
> 1375 Piccard Dr.,#250
> Rockville, MD 20850