This discussion is archived
8 Replies Latest reply: Dec 28, 2012 1:43 AM by 949210 RSS

ora-00932 order by case structure.

RPuttagunta Journeyer
Currently Being Moderated
Hi,

How to handle a ora-00932 in an order by with a case statement?

Eg:
SELECT orderlineid, productname
  FROM a
 ORDER BY CASE p_sorted_by
            WHEN 'product' THEN
             productname
            WHEN 'NONE' THEN
             orderlineid
          END;
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.
  • 1. Re: ora-00932 order by case structure.
    mtefft Journeyer
    Currently Being Moderated
    Try
    SELECT orderlineid, productname
      FROM a
     ORDER BY CASE p_sorted_by
                WHEN 'product' THEN
                 productname
                WHEN 'NONE' THEN
                 to_char(orderlineid,'99999999999999')
              END;
    or something similar.
  • 2. Re: ora-00932 order by case structure.
    RPuttagunta Journeyer
    Currently Being Moderated
    No, but, I DON'T want to order by a 'character' order by. It's different from a 'number' order by.


    Can I do something like this?
    SELECT orderlineid, productname
      FROM a
     ORDER BY CASE p_sorted_by
                WHEN 'product' THEN
                 2
                WHEN 'NONE' THEN
                 1
              END;
    and 2 And 1 represent the columns it's going to order by?
  • 3. Re: ora-00932 order by case structure.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    In any CASE expression, all of the THEN clauses must return the same data type.
    Mtefft showed you the best and simplest solution: convert the NUMBER to a VARCHAR2, so that both THEN clause do return the same data type.
    If, for some reason, you couldn't do that, then you could use two separate CASE expressions:
    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;
    If p_sorted_by='product', then the 2nd CASE expression will always return NULL, and it won't affect the sorting.
    If p_sorted_by='NONE', then the 1st CASE expression will always return NULL, and it won't affect the sorting.
  • 4. Re: ora-00932 order by case structure.
    RPuttagunta Journeyer
    Currently Being Moderated
    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'

    Number because, if I do to_char, then, it will order by like this:

    1
    10
    11
    2
    20
    21

    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?

    Edited by: RPuttagunta on Mar 23, 2011 1:20 PM
  • 5. Re: ora-00932 order by case structure.
    John Spencer Oracle ACE
    Currently Being Moderated
    you just need to change the format mask in mtefft's example to pad with leading zeroes like:
    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
    John
  • 6. Re: ora-00932 order by case structure.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    RPuttagunta wrote:
    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)?
    Exactly!
    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.
    You now have two ways to get around that, either of which should work:
    (1) have the single CASE expression consistently return a VARCHAR2, as Mtefft suggested. I still think this is the best solution.
    (2) use two separate CASE expressions, as in my query above. You might have to use this is, for example, you have NUMBERs like 6.022E230, which can't be formatted conveniently.
    Number because, if I do to_char, then, it will order by like this:

    1
    10
    11
    2
    20
    21

    etc, which is not we want.
    Right, that's why Mtefft used TO_CHAR to add leading spaces, so that
    '        2' would come before
    '      10'.
    What's wrong with Mtefft's solution? Post some sample data (CREATE TABLE and INSERT statements), and point out a couple of places where it produces the wrong results.
    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.
  • 7. Re: ora-00932 order by case structure.
    RPuttagunta Journeyer
    Currently Being Moderated
    What's wrong with Mtefft's solution?
    Now that you explained it, there is nothing wrong with Mtefft's solution. It was the correct one. I didn't read it right. The moment I saw a to_char() in there, I didn't look at the format mask at all.

    Thank you Mtefft, Frank and John.
  • 8. Re: ora-00932 order by case structure.
    949210 Newbie
    Currently Being Moderated
    use cast
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points