8 Replies Latest reply: Dec 28, 2012 3:43 AM by 949210 RSS

    ora-00932 order by case structure.

    RPuttagunta
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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