|
|
Archives of the TeradataForum
Message Posted: Wed, 02 Nov 2005 @ 15:47:38 GMT
Subj: | | Re: Recursive Queries and conditioning hierarchies |
|
From: | | Gottfried, Georg |
Oops, I missed some brackets in the last WHERE-clause
This is better:
WITH RECURSIVE ALL_HIER
( Level
,ID
,ID_CHAR
,PARENT_ID
,HIER_HIST
) as
(
select
0
,id
,id_char (format'9(10)') (CHAR(10))
,parent_id
,'' (VARCHAR(250))
from lu_hier
where parent_id is null
UNION ALL
select
p.Level + 1
,c.id
,c.id (format'9(10)') (CHAR(10)) as c_id_char
,c.prod_hier_code
,c.prod_hier_desc
,c.parent_prod_hier_code
,p.HIER_HIST||'-'||c_id_char||'-'
from ALL_HIER p
inner join lu_hier c
on p.id = c.parent_id
)
sel * from ALL_HIER r1
where r1.hier_hist NOT like '%-'|| ( 1
/* replace here the id you would like to exclude or join against a
lookup table */
(FORMAT '9(10)') (CHAR(10)) ) ||'-%'
order by 3,1
;
Regards,
Georg
| |