Re: Using Generic Comparator in Oracle ORDER BY

Much obliged... but your response kind of misses the mark. I need ORDER BY to use a custom PL/SQL comparator, not compare the results of a custom PL/SQL function.

In other words, I need ORDER by to sort so that "A-1.1" < "A-1.2" < "A-1.10" (but that works at any arbitrary depth).

A pure numeric ordering won't work (because "A" is not numeric) and a lexicographic ordering will not work ("A-1.2" > "A.1.10" in lex ordering). I can write functions to "get" the number at a given position, then write SQL to ORDER BY this function for postion1, for position2, position3, ..., position n, where n is some arbitrary depth.  But that yields a very complicated ORDER BY clause, and fails for depth n+1.

The only out I can conceive of is a function that maps the input domain to a range of values that *can* be ordered numerically or lexicographically... Such a function is doable (e.g. a PL/SQL function to convert all embedded numbers to a base-8 value in a fixed length string left-padded with zeroes), but talk about an overly-sophisticated workaround! And still technically wrong, as I might pick a base-8 string length that is too small!

The syntax I'd be looking for would be akin to:

ORDER BY <column_name> [<comparator>] [ASC | DESC]

Which, as far as I know, does not exist (comparator is implicitly NUMERIC_LESS_THAN if <column_name> is numeric, and LEXICOGRAPHIC_LESS_THAN otherwise... no flexibility!) Received on Wed Oct 26 2005 - 11:51:28 CDT