Thomas Markus wrote:
> i want to emulate the 'connect by' statement from Oracle.
>
> my statement looks like this:
>
> ------------------------------------------------------
> declare cursortest cursor for
> with recursive PX (IDCOL,REFCOL,LVL) as
> (
> select
> ID, REFERENCE, 1
> from
> TableA
> where
> REFERENCE=0
> union all
> select
> ID, REFERENCE, LVL+1
> from
> TableA, PX
> where
> REFERENCE=IDCOL
> )
> select
> LPAD(C.TRANSLATE_TEXT,4*(A.LVL-1)+1,'.',40),
> A.IDCOL,A.REFCOL,A.LVL
> from
> PX A,
> TableA B,
> LanguageTable C
> where
> A.IDCOL=B.ID AND
> B.TEXT_ID=C.TEXT_ID AND
> C.LNG_ID=1
> ------------------------------------------------------
>
> this should show a tree-structure with language dependend strings.
>
> Text1
> ....Text11
> ....Text12
> ........Text121
> Text2
> ....Text21
>
>
> How can I order the rows correct?
Not tested. but what about:
declare cursortest cursor for
with recursive PX (IDCOL,ALLIDS,REFCOL,LVL) as
(
select
ID, ID, REFERENCE, 1
from
TableA
where
REFERENCE=0
union all
select
ID, ALLIDS || ID, REFERENCE, LVL+1
from
TableA, PX
where
REFERENCE=IDCOL
)
select
LPAD(C.TRANSLATE_TEXT,4*(A.LVL-1)+1,'.',40),
A.IDCOL,A.REFCOL,A.LVL
from
PX A,
TableA B,
LanguageTable C
where
A.IDCOL=B.ID AND
B.TEXT_ID=C.TEXT_ID AND
C.LNG_ID=1
order by ALLIDS
Something in that direction may help
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
