Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Sort order

464177Nov 1 2005 — edited Nov 3 2005
Your help is appreciated. Basically I would like to sort the character in alphabetical order regardless to the case. However, if the alphabet is the same, then I would like to sort by lower to upper case.

For example, I have the followings in my table:

aA
Aa
ab
aC
bC
Bz
C1
Cz

and I would like to sort this column in the following order, is there a way I can do this?

aA
ab
aC
Aa
bC
Bz
C1
Cz

Thanks

Comments

SomeoneElse
I don't have an answer, but this is confirmed on 10.2.0.4: 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SQL> select * from dual model
  2   dimension by(0 as d)
  3   measures(1 as m)
  4   rules(
  5   m[any] = case when not m[1] is present then 1 end);
select * from dual model
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
1 person found this helpful
666352
can you chech you alert trace , may be you can find more details.
SQL> show parameter background_dump_dest;
1 person found this helpful
Hoek
I get:
ops$me%DB>  select * from dual model
  2    dimension by(0 as d)
  3    measures(1 as m)
  4    rules(
  5    m[any] = case when not m[1] is present then 1 end);
 select * from dual model
               *
ERROR at line 1:
ORA-03002: operator not implemented


Elapsed: 00:00:00.03
ops$me%DB> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
1 person found this helpful
Aketi Jyuuzou
Thankys for replys.

I checked traceFile which is below.
Fri Mar 20 23:22:54 2009
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_948.trc:
ORA-07445: 例外が検出されました: コア・ダンプ 
[ACCESS_VIOLATION] [_qcss_process_expr+226] [PC:0x1E24A46] [ADDR:0x18] [UNABLE_TO_READ] []
umm I think I will use presentv insted of is present
Solomon Yakobson
Answer
[Bug 6530708|https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=6530708&p_database_id=BUG]

SY.
Marked as Answer by Aketi Jyuuzou · Sep 27 2020
Aketi Jyuuzou
Oh thank you,
someday,I will get metaLink accout.
I will see that.
666352
But you can use this code . 
 1  select * from dual model
 2   dimension by(0 as d)
 3   measures(1 as m)
 4   rules(
 5*  m[any] = case when  m[1] is present then null else 1 end)
 6  /

        D          M
--------- ----------
        0          1
1 person found this helpful
Laurent Schneider
I created that bug at the time I was writting my chapter on model...

Strangely the error changed in 11.2.0.2 
select * from dual model
 dimension by(0 as d)
 measures(1 as m)
 rules(
 m[any] = case when not(m[1] is  present) then 1 end)
              *
Error at line 1
ORA-03002: operator not implemented
Edited by: Laurent Schneider on Jan 13, 2011 11:32 AM

the ORA-3002 was already mentioned, sorry
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 1 2005
Added on Nov 1 2005
5 comments
8,663 views