The second a SELECT statement and you could craft your own.
SELECT 'ALTER ' || object_type || ' '|| object_name ||' COMPILE;' FROM user_objects WHERE object_type IN ('FUNCTION', 'PACKAGE', 'PROCEDURE', 'TRIGGER', 'VIEW') ORDER BY DECODE(object_type, 'VIEW','A', 'FUNCTION','B', 'PROCEDURE', 'C', 'PACKAGE','D', 'Z');
SELECT DECODE((SELECT 'x' FROM DUAL), ( SELECT 'x' FROM DUAL), (SELECT 'Morgan' FROM dual)) AS RESULT FROM (SELECT 'm' FROM dual) WHERE (SELECT 1 FROM dual) = (SELECT 1 FROM dual) AND (SELECT 2 FROM dual) BETWEEN (SELECT 1 FROM dual) AND (SELECT 3 FROM dual) AND NVL((SELECT NULL FROM dual ), (SELECT 'z' FROM dual)) = (SELECT 'z' FROM dual) ORDER BY (SELECT 1 FROM dual);
Solomon Yakobson wrote:Actually, documentation could be better:
It is all documented: ORDER BY.
SQL> select job,ename from emp order by 1.2 2 / JOB ENAME --------- ---------- ANALYST SCOTT ANALYST FORD CLERK MILLER CLERK JAMES CLERK SMITH CLERK ADAMS MANAGER BLAKE MANAGER JONES MANAGER CLARK PRESIDENT KING SALESMAN TURNER JOB ENAME --------- ---------- SALESMAN MARTIN SALESMAN WARD SALESMAN ALLEN 14 rows selected. SQL> select job,ename from emp order by 1.9 2 / JOB ENAME --------- ---------- ANALYST SCOTT ANALYST FORD CLERK MILLER CLERK JAMES CLERK SMITH CLERK ADAMS MANAGER BLAKE MANAGER JONES MANAGER CLARK PRESIDENT KING SALESMAN TURNER JOB ENAME --------- ---------- SALESMAN MARTIN SALESMAN WARD SALESMAN ALLEN 14 rows selected. SQL> select job,ename from emp order by 2.3 2 / JOB ENAME --------- ---------- CLERK ADAMS SALESMAN ALLEN MANAGER BLAKE MANAGER CLARK ANALYST FORD CLERK JAMES MANAGER JONES PRESIDENT KING SALESMAN MARTIN CLERK MILLER ANALYST SCOTT JOB ENAME --------- ---------- CLERK SMITH SALESMAN TURNER SALESMAN WARD 14 rows selected. SQL>
987236 wrote:There's no pseduo-column involved. (Pseudo-columns are something that Oracle provides for you, without you having to spell out what you want. ROWID and LEVEL are examples of pseudo-columns. In this example, 2+3 is an Expression . Like pseudo-columns, expressions can usually appear in the ORDER BY clause, even though they are not actually stored in the table.)
Considering that all the column names and table name is valid, what happens internally when the following query is run?
SQL> SELECT STU_ID, STU_NAME FROM STUDENT ORDER BY 2+3, STU_ID;
What i found out was the rows get ordered in ascending order of STU_ID. It's quite clear that it is ignoring the expressing 2+3, so my doubts are:
(a) Is it appending any pseudo-column to the table with 2+3 getting evaluated to 5
and it's also equivalent to
ORDER BY 2 + 3 , 'foo' , SYSDATE , NULL , stu_id
None of the expressions except stu_id vary from row to row, so none of them affect the sorting.
ORDER BY stu_id
(b) If there exist a 5th column in the table student and we specify it in the select statement's select list (5 column names) then why its not considering it as 5 and sorting the data according that?As Solomon said, only number literals are taken to mean a column. 5 is a number literal, so
means "sort by the 5th column", but 2 + 3 is not a literal (it's an expression that happens to include a couple of literals), so
ORDER BY 5
does not refer to the 5th colunmn.
ORDER BY 2 + 3
(c) Is it true that, any expression we write is getting evaluated to NULL? Then is it that ORDER BY NULL evaluates to no ordering and the parser searches for next column-name or position (if specified) ?No, when you way "ORDER BY x", the rows with lower values of x come first, followed by rows with higher values of x. Rows with the same value of x will be together, in no particular order with respect to each other (unless there is a tie-breaker expression later in the ORDER BY clause.
(d) if neither then, what's the reason of the ignorance?