Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C00AC9F9AC4 for ; Wed, 5 Sep 2007 11:46:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 79457-08 for ; Wed, 5 Sep 2007 11:46:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.wazagua.com (mail.wazagua.com [209.34.245.246]) by postgresql.org (Postfix) with ESMTP id A91019F994E for ; Wed, 5 Sep 2007 11:46:16 -0300 (ADT) Received: from jfradkin64 ([24.173.145.10]) by mail.wazagua.com with Microsoft SMTPSVC(6.0.3790.3959); Wed, 5 Sep 2007 10:49:32 -0400 From: "Joel Fradkin" To: Subject: recursive query Date: Wed, 5 Sep 2007 10:47:29 -0400 Message-ID: <003201c7efcb$af931500$837ba8c0@jfradkin64> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0033_01C7EFAA.28817500" X-OriginalArrivalTime: 05 Sep 2007 14:49:32.0093 (UTC) FILETIME=[F7F756D0:01C7EFCB] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.385 tagged_above=0 required=5 tests=AWL=-0.001, BAYES_40=-0.185, HTML_MESSAGE=0.001, HTML_NONELEMENT_30_40=0.001, RATWARE_MS_HASH=1.398, RATWARE_OUTLOOK_NONAME=2.171 X-Spam-Level: *** X-Archive-Number: 200709/52 X-Sequence-Number: 29265 This is a multi-part message in MIME format. ------=_NextPart_000_0033_01C7EFAA.28817500 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I am trying to do a hierarchy design. We currently have it hard coded to = 4 levels and the location table has an id field for each level where the = data has a location id. This allows easy access by level (to restrict who see what) and easy to arrogate for reporting by division, region, district, and location. I am trying to make it more flexible some companies don't use 4 levels = some want more, some have different sets of users and would like their own hierarchy. My idea is to have a virtual hierarchy (all share the location record = which is the lowest level and a separate file that will be linked with a table that has the locationid and the hierarchy id to enable multiple = hierarchy). I did a bit of research and found some code to do recursive sql (but it = was IBM and I am not sure I even got it right for postgres). =20 create or replace temp view test (clientnum,id ,parentid,descr, level) = as ( select h.clientnum,h.id ,h.parentid,h.descr,0 as level from tblhyerarchy h where parentid =3D0=20 union all select h1.clientnum,h1.id ,h1.parentid,h1.descr,h.level +1 as level from tblhyerarchy h1 inner join test h on h1.parentid =3Dh.id=20 where h.level < 5 ); select * from test =20 but get a=20 ERROR: infinite recursion detected in rules for relation "test" SQL = state: 42P17 =20 I am basing on a table created with CREATE TABLE tblhyerarchy ( clientnum character varying(16) NOT NULL, id integer NOT NULL, parentid integer NOT NULL, descr character varying(250), CONSTRAINT pk_tblhyerarchy PRIMARY KEY (clientnum, id) )=20 WITH OIDS; =20 =20 Any help is much appreciated. I am also a little confused how to do a crosstab on the output. In the end I will want to get at a data record that has the location id = and then link to the hierarchy with each level of the hierarchy present for aggregation etc. =20 Joel Fradkin =20 Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 =20 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized = review, use, disclosure or distribution is prohibited. If you are not the = intended recipient, please contact the sender by reply email and delete and = destroy all copies of the original message, including attachments. =20 ------=_NextPart_000_0033_01C7EFAA.28817500 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

I am trying to do a hierarchy design. We currently = have it hard coded to 4 levels and the location table has an id field for each = level where the data has a location id.

This allows easy access by level (to restrict who see = what) and easy to arrogate for reporting by division, region, district, and = location.

I am trying to make it more flexible some companies = don’t use 4 levels some want more, some have different sets of users and would = like their own hierarchy.

My idea is to have a virtual hierarchy (all share the location record which is the lowest level and a separate file that will = be linked with a table that has the locationid and the hierarchy id to = enable multiple hierarchy).

 I did a bit of research and found some code to = do recursive sql (but it was IBM and I am not sure I even got it right for = postgres).

 

create or replace = temp view test (clientnum,id ,parentid,descr, level) as (

select   = h.clientnum,h.id ,h.parentid,h.descr,0 as level

 from = tblhyerarchy h where parentid =3D0

union = all

select   h1.clientnum,h1.id ,h1.parentid,h1.descr,h.level +1 as = level

 from = tblhyerarchy h1

inner join test h = on h1.parentid =3Dh.id

where h.level < = 5

);

select * from = test

 

but get a =

ERROR: infinite = recursion detected in rules for relation "test" SQL state: = 42P17

 

I am basing on a = table created with

CREATE TABLE = tblhyerarchy

(

  clientnum = character varying(16) NOT NULL,

  id integer = NOT NULL,

  parentid = integer NOT NULL,

  descr = character varying(250),

  CONSTRAINT = pk_tblhyerarchy PRIMARY KEY (clientnum, id)

) =

WITH = OIDS;

 

 

Any help is much appreciated. I am also a little = confused how to do a crosstab on the output.

In the end I will want to get at a data record that = has the location id and then link to the hierarchy with each level of the = hierarchy present for aggregation etc.

 

Joel Fradkin

 

Wazagua, Inc.
2520 Trailmate = Dr
Sarasota, Florida 34= 243
Tel.  941-753-7111 ext 305

 

jfradkin@wazagua.com<= /font>
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced = tools.
© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and = may contain confidential and privileged information.  Any unauthorized = review, use, disclosure or distribution is prohibited.  If you are not the intended recipient, please contact the sender by reply email and delete = and destroy all copies of the original message, including = attachments.

 

------=_NextPart_000_0033_01C7EFAA.28817500--