Does progress 4GL language support hierarchical queries?

Question

The simple question subscribed on progress site: Does progress 4GL language support hierarchical queries like Oracle (Connect by clause) or Sql Server(CTE)?

I have the following table:

Name             parent
-----------------------
Elizabeth II     null
Charles           Elizabeth II
Andrew          Elizabeth II
Edward           Elizabeth II
Harry              Charles
William           Chales
James             Edward
George           William

Is there a script in progress that will generate the following output?

Elizabeth II
     |_Charles
          |_William
               |_George
          |_Harry
     |_Andrew
     |_Edward
          |_James

Answer 1

Since Progress 4GL (actually ABL since a couple years) is a complete turing complete language you can. However perhaps not in a single query...

This recursive example does it, you could do in a number of different ways. You can start with this code but you might need to have more error checks etc.

DEFINE TEMP-TABLE ttPerson NO-UNDO
    FIELD PersonName   AS CHARACTER FORMAT "x(20)"
    FIELD PersonParent AS CHARACTER.

/* A procedure for loading example data */
PROCEDURE createPerson:
    DEFINE INPUT  PARAMETER pcName AS CHARACTER   NO-UNDO.
    DEFINE INPUT  PARAMETER pcParent AS CHARACTER   NO-UNDO.

    CREATE ttPerson.
    ASSIGN 
        ttPerson.personName   = pcName
        ttPerson.personParent = pcParent.

END.

/* Load some data */
RUN createPerson("Elizabeth II", "").
RUN createPerson("Charles", "Elizabeth II").
RUN createPerson("Andrew", "Elizabeth II").
RUN createPerson("Edward", "Elizabeth II").
RUN createPerson("Harry", "Charles").
RUN createPerson("William", "Charles").
RUN createPerson("James", "Edward").
RUN createPerson("George", "William").

/* Define a frame where the result will be displayed */
DEFINE FRAME f1 ttPerson.personName WITH 20 DOWN.

/* The recursive prodecure */
/* pcPerson - the person where to start track heritage (or perhaps it should have been lineage?*/
/* piDepth, just to format the output */
PROCEDURE trackHeritage:
    DEFINE INPUT  PARAMETER pcPerson AS CHARACTER   NO-UNDO.
    DEFINE INPUT  PARAMETER piDepth  AS INTEGER     NO-UNDO.

    piDepth = piDepth + 1.
    /* Find the tracked person */
    FIND FIRST ttPerson NO-LOCK WHERE ttPerson.personName = pcPerson NO-ERROR.
    IF AVAILABLE ttperson THEN DO:

        DISPLAY FILL(" ", piDepth) + "|_" + ttPerson.personName @ ttPerson.personName WITH FRAME f1.

        DOWN 1 WITH FRAME f1.

        /* Track all available children to the person */
        FOR EACH ttPerson NO-LOCK WHERE ttPerson.personParent = pcPerson:
            RUN trackHeritage(ttPerson.personName, piDepth).
        END.
    END.
END.
/* Start tracking */
RUN trackHeritage("Elizabeth II", 0).

MESSAGE "Done" VIEW-AS ALERT-BOX.