Re: Oracle 'connect by prior' now eaiser in 7.3?

Hello!

Can anyone help me to use connectby() with my structure?
I cannot change the name of tables. It is a import!
------------------------------------------
Thomas Wegner

"Thomas Wegner" <tomaten(at)t-online(dot)de> schrieb im Newsbeitrag
news:bm1p2g$98o$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
> Hello,
>
> i have a table like this:
>
> CREATE TABLE "public"."WINUSER" (
> "ID_WINUSER" INTEGER NOT NULL,
> "STATUS" INTEGER NOT NULL,
> "CUSTOMERID" VARCHAR(8) NOT NULL,
> "CUSTOMERPW" VARCHAR(100) NOT NULL,
> "EMAIL" VARCHAR(100) NOT NULL,
> "REF_ID_WINUSER" INTEGER,
> PRIMARY KEY("ID_WINUSER"),
> ) WITH OIDS;
>
> and will get the tree from this to fields:
>
> "ID_WINUSER" INTEGER NOT NULL,
> "REF_ID_WINUSER" INTEGER,
>
> i write this sql:
>
> SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
> '"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
>
> and get this error:
>
> ERROR: Query-specified return tuple not valid for Connectby: wrong number
> of columns
>
> How is the correct use of connectby() for me?
> ------------------------------------------
> Thomas Wegner
>
> "Joe Conway" <mail(at)joeconway(dot)com> schrieb im Newsbeitrag
> news:3F6FC51F(dot)6090105(at)joeconway(dot)com(dot)(dot)(dot)
> > Merrall, Graeme wrote:
> > > Am I right in thinking that recursive procedures and procs returning
row
> > > sets would allow us to better emulate this behaviour? As anyone looked
> > > at it yet?
> > >
> >
> > See connectby() in contrib/tablefunc. Someone was working on SQL99
> > recursive queries but it didn't get done for 7.4 -- perhaps it will be
> > in 7.5. In the meantime, connectby() is in 7.3 and might work for you.
> >
> > HTH,
> >
> > Joe
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
>