RE: ORDER BY in CONNECT BY
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