Here orderlineid is 'NUMBER' and productname is 'VARCHAR2' and want to change the way the cursor orders by based on a variable. One with number another with varchar2.
SELECT orderlineid, productname FROM a ORDER BY CASE p_sorted_by WHEN 'product' THEN productname WHEN 'NONE' THEN orderlineid END;
and 2 And 1 represent the columns it's going to order by?
SELECT orderlineid, productname FROM a ORDER BY CASE p_sorted_by WHEN 'product' THEN 2 WHEN 'NONE' THEN 1 END;
If p_sorted_by='product', then the 2nd CASE expression will always return NULL, and it won't affect the sorting.
SELECT orderlineid, productname FROM a ORDER BY CASE WHEN p_sorted_by = 'product' THEN productname END , CASE WHEN p_sorted_by = 'NONE' THEN orderlineid END;
SQL> var srt varchar2(10); SQL> exec :srt := 'NAME' PL/SQL procedure successfully completed. SQL> WITH T AS ( 2 select 1 num, 'A' name from dual union all 3 select 235, 'C' from dual union all 4 select 10 , 'D' from dual union all 5 select 12, 'X' from dual union all 6 select 33, 'Q' from dual union all 7 select 678, 'L' from dual) 8 select num, name from t 9 order by case :srt when 'NAME' then name 10 when 'NUM' then TO_CHAR(num, '099999999') END; NUM N ---------- - 1 A 235 C 10 D 678 L 33 Q 12 X SQL> exec :srt := 'NUM' PL/SQL procedure successfully completed. SQL> / NUM N ---------- - 1 A 10 D 12 X 33 Q 235 C 678 L
Thank you Frank.
So, what you mean to say is in your example, it will never throw
ORA-06592: CASE not found while executing CASE statement (the 'else' part isn't written, so, it would return null)?
Basically, depending on the scenario, I want to be able to order by a 'number' (orderlineid) or 'character'Right. So you can't use a single CASE expression, because any one CASE expression must return a single data type, either a NUMBER or a VARCHAR2.
Number because, if I do to_char, then, it will order by like this:Right, that's why Mtefft used TO_CHAR to add leading spaces, so that
etc, which is not we want.
Also, my second post, will it work? I mean, will it take the '1' and '2' as columns in the 'select' statement?Try it and see. You've got the sample data, so you can test it. Until you post CREATE TABLE and INSERT statements for your data, we can't.
SELECT CASE WHEN (MOBILE IS NULL AND LANDLINE IS NULL) THEN 'both are null' WHEN (LANDLINE IS NOT NULL AND MOBILE IS NOT NULL) THEN LANDLINE||MOBILE WHEN MOBILE IS NULL THEN LANDLINE WHEN LANDLINE IS NULL THEN cast(mobile as varchar2(30)) end from reach_out_contact