Re: Sort Order in Nested query

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 01 Feb 2013 11:12:37 +0100
Message-ID: <510B9515.5000303_at_roughsea.com>



On 01/31/2013 06:03 PM, Fergal Taheny wrote:
> Hi,
> I have a query and I want the output ordered. I also want to assign a key
> to the output using a sequence. I don't care about the ordering in which
> the key is assigned.
>
> *I can't do this:*
>
> select seq.nextval,USER_ID,CREATED from dba_users order by CREATED;
> *
> ERROR at line 1:
> ORA-02287: sequence number not allowed here
Fergal,

     Is the SELECT the whole story? Because I don't really see the point of using a sequence here. A sequence is a database object. When you invoke nextval you change the state of your database. For me, sequences are always associated to DML statements, which also change the state of the database (I have already seen examples similar to what you are trying to do, but for me they are pretty meaningless). What you are trying to do would make sense if it were for an INSERT ... SELECT ... though. But then, why the ORDER BY, especially if you don't care about the order?
Somehow, things don't quite fit. For a SELECT I'd rather use rownum as you also do, or (better IMHO) row_number() over (), possibly in combination with SYS_GUID() since concurrent accesses seem to be an issue.

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>



--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 01 2013 - 11:12:37 CET

Original text of this message