How to order case insensitive ASC or DESC, with DISTINCT and UNION

Question

How to order case insensitive ASC or DESC for P / L sql 11g. this basic question is p / l sql, but I can’t find a good answer on Google, please tell me how to sort the register to no avail.

this is what i tried

   SELECT  DISTINCT
            asssss,
            saas_acc
      FROM DUAL
   UNION SELECT '--ALL--','ALL' FROM DUAL
   ORDER BY  upper(asssss) ASC ;

who gave me ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Answer 1

DISTINCT actually filtered UNIQUE content in the result set with any expressions specified in the SELECT clause.

We cannot order it using another expression or column name. See an example here.

SQL> l
  1  SELECT DISTINCT  (col1),(col2)
  2  FROM
  3    ( SELECT 'Hello' col1,'World' col2 FROM DUAL
  4    UNION ALL
  5    SELECT 'HELLO','WORLD' FROM DUAL
  6*   )
SQL> /

COL1  COL2
----- -----
HELLO WORLD
Hello World

You can see that DISTINCThere CASE SENSITIVE(2 lines displayed)

---

, UPPER() .

SQL> l
  1  SELECT DISTINCT UPPER (col1),UPPER(col2)
  2  FROM
  3    ( SELECT 'Hello' col1,'World' col2 FROM DUAL
  4    UNION ALL
  5    SELECT 'HELLO','WORLD' FROM DUAL
  6*   )
SQL> /

UPPER UPPER
----- -----
HELLO WORLD

, .

---

. - DISTINCT Resultset, / DISTINCT.

, DISTINCT COL1,COl2, COL1 COL2/.. COL3 UPPER(COL1), UPPER() , DISTINCT.

---

,

, ORDER , DISTINCT !

SELECT  DISTINCT
            UPPER(asssss),
            saas_acc
      FROM DUAL
   ORDER BY  upper(asssss) ASC ;

UNION , , .

SELECT * FROM    
(
  SELECT  DISTINCT asssss as asssss,
          saas_acc
  FROM DUAL
 UNION
 SELECT '--ALL--','ALL' FROM DUAL
)
ORDER BY  upper(asssss) ASC ;

---

, , - / ORDER BY, SELECT. () . DISTINCT COL1,COl2 . ORDER BY UPPER(COL1), SELECT, NOT . , !

Answer 2

, NLS_COMP ANSI

   NLS_COMP=ANSI

: http://www.orafaq.com/node/999

Answer 3

upper lower.

order by upper(columnName)

Update1

Try removing the sentence order-byfrom your query that will give you the correct error, which is equal ORA-00904: "SAAS_ACC": invalid identifier. Thus, you can search googlefor this error or ask another question in SO.

Also see how to use union order .

Answer 4

The simplest option would be to sort by upper or lower case column

ORDER BY UPPER( column_name )