RE: ORDER BY in CONNECT BY

From: Tony Scott <asc_at_cix.compulink.co.uk>
Date: Thu, 10 Feb 1994 17:59:58 GMT
Message-ID: <CL0snz.6E4_at_cix.compulink.co.uk>


I did see your message, but after all the research I had done (which admittedly was a year ago) I just couldn't believe it would be that easy and as I had mine prepared I sent it anyway. Having now tried your solution, I most heartily and humbly apologise for doubting you.

I notice the following:
My application is a thesaurus, with preferred and non-preferred terms identified by 'p' and 'n' flags. This solution does not work if you qualify the search (with WHERE TYP = 'p') so that only a subset of the data is output. I created a temporary table by CREATE <temp_table> AS SELECT * FROM <first_table> WHERE TYP = 'p'; Interestingly, it works on the temporary table even if the WHERE TYP = 'p' clause is included.

Also a suggestion:
In a real database then WHERE name > chr(31) might be preferable
(assuming ascii environment).

I wonder if we will see the discussion you referred to in the UK bulletin.

Tony Scott
(asc_at_cix.compulink.co.uk)

The correspondence on this was as follows: moebius_at_athene.informatik.uni-bonn.de (Dirk Moebius) wrote (in part): I have a question concerning PL/SQL.
How do I sort the hierarchical output of records, that have been fetched by a CONNECT BY - Statement?
<followed by an example deleted>

noel_at_omega.univ-lille1.fr (Yves Noel) wrote (in part): In your exemple, you must :

  • create an index on the name
  • force the ORACLE optimizer to use this index like access way. i.e. SQL> create index emp.name on emp(name); .. SQL> select lpad ........ from emp connect by prior ... and name > 'A' ^^^^^^^^^^^^^^ start with ... ;

I, that is, asc_at_cix.compulink.co.uk ("Tony Scott") wrote: I have battled with this problem for a thesaurus and my conclusion is that you can't do it in SQL*Plus. I am about to do it using SQL*Reportwriter. The Oracle Response centre faxed me some horrendous looking solution that they said they didn't understand themselves. It involves using PL/SQL and a table to simulate a stack. The fax ends with the words "I have not tested this answer: I am assuming it is correct" Received on Thu Feb 10 1994 - 18:59:58 CET

Original text of this message