Re: Oracle Conditional CONNECT BY

From: Leo J. Hart IV <leo.hart_at_nospamplease.fmr.com>
Date: Tue, 19 Mar 2002 09:42:43 -0500
Message-ID: <q4Il8.3$M3.46@news-srv1.fmr.com>

Hi Edzard,

Thanks for your quick reply. It turns out I can't really do what I wanted without running into an infinite loop. So instead I wrote a procedure to bring in the main website's links and any links in a different website that are a direct child to the first website:

/***************************************************************************
********************************/

PROCEDURE GET_SITE_MAP_H
( websiteID IN NUMBER DEFAULT NULL,

siteMap OUT REFCURSOR)
IS

        siteMapTable SITEMAPTABLETYPE := SITEMAPTABLETYPE();
        subSiteMapTable SITEMAPTABLETYPE := SITEMAPTABLETYPE();

        CURSOR siteMapCursor IS
            SELECT
                LEVEL as depth,
                name,

DECODE(is_parent||'!'||link_website_id||link_element_id||link_url,'N!', NULL,

                        DECODE(target,


                            '_top',

                                '<a

href="'||GET_ELEMENT_URL(link_website_id, link_element_id, link_url)||'" target="_top">',

'_blank',

'<a

href="'||GET_ELEMENT_URL(link_website_id, link_element_id, link_url)||'" target="_blank">',

'<a href="/default.asp?elementID='||id||'"
'||'target="_top">')) AS URL,

                 link_website_id,
                 link_element_id
            FROM cfit_element
            WHERE on_toc = 'Y'
            START WITH parent_id IS NULL AND website_id = websiteID
            CONNECT BY PRIOR id = parent_id
            ORDER BY GET_GLOBAL_ORDER_NUMBER(id, website_id,

GET_HIGHEST_ORDER_NUMBER_LEN(website_id)) ASC;

BEGIN

        FOR siteMapRecord IN siteMapCursor LOOP
            siteMapTable.EXTEND;
            siteMapTable(siteMapTable.COUNT) :=
                SITEMAPRECORDTYPE(
                    siteMapRecord.depth,
                    siteMapRecord.name,
                    siteMapRecord.URL);
            IF siteMapRecord.link_website_id IS NOT NULL OR
siteMapRecord.link_element_id IS NOT NULL THEN
                subSiteMapTable :=
                    GET_SITE_MAP_H_SUB(siteMapRecord.link_website_id,
                        siteMapRecord.link_element_id, siteMapRecord.depth);
                FOR i IN 1..subSiteMapTable.COUNT LOOP
                    siteMapTable.EXTEND;
                    siteMapTable(siteMapTable.COUNT) := subSiteMapTable(i);
                END LOOP;
            END IF;
        END LOOP;

        OPEN siteMap FOR
            SELECT depth, name, url
            FROM TABLE(CAST(siteMapTable AS SITEMAPTABLETYPE));

END;

/***************************************************************************
********************************/

FUNCTION GET_SITE_MAP_H_SUB
( websiteID IN NUMBER DEFAULT NULL,

        parentID IN NUMBER DEFAULT NULL,
        currentDepth IN NUMBER DEFAULT NULL)
    RETURN SITEMAPTABLETYPE IS
        subSiteMapTable SITEMAPTABLETYPE := SITEMAPTABLETYPE();

        CURSOR subSiteMapCursor IS
            SELECT
                currentDepth + LEVEL as depth,
                name,
                DECODE(target,


                            '_top',

                                '<a

href="'||GET_ELEMENT_URL(link_website_id, link_element_id, link_url)||'" target="_top">',

'_blank',

'<a

href="'||GET_ELEMENT_URL(link_website_id, link_element_id, link_url)||'" target="_blank">',

'<a href="'||GET_ELEMENT_URL(website_id, NULL,
NULL)||'default.asp?elementID='||id||'" '||'target="_top">') AS URL,

                 link_website_id,
                 link_element_id
            FROM cfit_element
            WHERE on_toc = 'Y'
            START WITH NVL(parent_id,-1) = NVL(parentID,-1) AND website_id =
websiteID
            CONNECT BY PRIOR id = parent_id
            ORDER BY GET_GLOBAL_ORDER_NUMBER(id, website_id,

GET_HIGHEST_ORDER_NUMBER_LEN(website_id)) ASC;

BEGIN

        FOR subSiteMapRecord IN subSiteMapCursor LOOP
            subSiteMapTable.EXTEND;
            subSiteMapTable(subSiteMapTable.COUNT) :=
                SITEMAPRECORDTYPE(
                    subSiteMapRecord.depth,
                    subSiteMapRecord.name,
                    subSiteMapRecord.URL);
        END LOOP;
        RETURN subSiteMapTable;

END;

/***************************************************************************
********************************/

Thanks for your help!
Leo

"Edzard" <edzard_at_volcanomail.com> wrote in message news:5d75e934.0203170429.727f5b0e_at_posting.google.com...

> Hi,
>
> Although I'm not familiar with the subject "website building
> application", I like to react to your message. The procedural code
> that you listed is clear to  read, also without the particular subject
> knowledge. It is a nice demo of the use of records and tables within
> PL/SQL,  if I may judge.
>
> But I wonder why the CONNECT BY was not suited. I assume that a
> solution within SQL is peferred  over the use of procedural code. From
> your code, the following SQL can be derived:
>
> CONNECT BY PRIOR.link_website_id IS NOT NULL
> AND website_id = PRIOR.link_website_id
> AND NVL (parent_id, -1) = NVL (PRIOR.link_element_id, -1))
> OR PRIOR.link_website_id IS NULL
> AND PRIOR.is_parent = 'Y'
> AND website_id = PRIOR.website_id
> AND NVL (parent_id, -1) = NVL (PRIOR.parent_id, -1)
>
> As I say, I'm not familiar with what is really meant here, but I hope
> that this SQL correctly expresses the application logic.  I also
> assume that you started with a similar expression, maybe using an
> equivalent DECODE or CASE construction. Why did that not succeed? Is
> there some restriction on the clauses in a CONNECT BY clause?  I'm
> only aware of a  restriction on the  FROM part, you can not use
> CONNECT BY on a join.  But that can easily be worked around by
> including the join in a view. May be the use of a view is a solution
> here too.
>
> Like to hear,
>
> Edzard

Received on Tue Mar 19 2002 - 08:42:43 CST

Re: Oracle Conditional CONNECT BY - Possible?