This content has been marked as final. Show 5 replies
What you are trying to do appears to be programmatic nonsense but there are a couple of ways to write a dynamic WHERE clause in addition to the use of Native Dynamic SQL. Here are two examples from Morgan's Library (http://www.morganslibrary.org/library.html).
The first uses DECODE and you could alternatively use CASE.
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.
position Specify position to order rows based on their value for the expression in this position of the select list. The position value must be an integer.
First of all, it should say position must be a literal. Secondly, it can be any numeric literal. It looks like Oracle truncates it to a whole number:
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>
Welcome to the forum!
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
It's sorting first by the NUMBER 5 (= 2 + 3), not the 5th column. Since the NUMBER 5 has the same value on every row, every row ties for first place. The result of sorting by any constant is the same; the results are the same as not including that exrpression in the ORDER BY clause. That is the ORDER BY clause you posted is equivalent to
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.
That's exactly what happens when you say "ORDER BY NULL". NULL has the same value (actually, the same lack of any value) on all rows, so all the rows have the same value of x, and they will appear in no particular order.
(d) if neither then, what's the reason of the ignorance?