Re: Sort Order in Nested query
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-lReceived on Fri Feb 01 2013 - 11:12:37 CET