Re: Report6i: Order by CLOB column?

Tiago Rocha wrote:

> Hi,
>
> I have a very simple report made with Reports6i, report is fine, but
> user wants that it be ordered by the clob column, but clob column
> cannot be used on a order by... Does anyone knows any trick or will I
> have to convert the CLOB to LONG?
>
> Could not find an answer browsing metalink...
>
> DB is 8.1.7
>
> thanks in advance.

[Quoted] Users the bane of our existance. ;-)

Rather than trying to create deprecated LONGs or whatever ... have your end-users clearly define what they intend by sorting. Then, with that criteria in hand use the DBMS_LOB built-in package to create a pseudo-column that you can use in the ORDER BY clause.

Here's an example of sorting with a pseudo-column that does not involve a CLOB but the principle is the same.

SELECT address, TO_NUMBER(TRIM(TRANSLATE(address, '0ABCDEFGHIJKLMNOPOQRSTUVWXYZabcdefghijklmnopqrstuvwxyz.,','0'))) SORTCOL FROM test
ORDER BY SORTCOL; Note that SORTCOL does not exist in the data but is created from the address column purely for sorting purposes.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)