4 Replies Latest reply on Jul 22, 2009 9:47 AM by Sven W.

    Query Builder - sort order causing ORA-00979 error

    712342
      Hello,
      I'm having an issue with the "sort order" clause in the query builder tool when using a function on the same column.

      I've build a simple query in the gui and the following statement is displayed in the SQL Window
      select     "DEMO_ORDER_ITEMS"."ORDER_ID" as "ORDER_ID",
           sum(DEMO_ORDER_ITEMS.QUANTITY) as "QUANTITY"
      from     "DEMO_ORDER_ITEMS" "DEMO_ORDER_ITEMS"
      group by DEMO_ORDER_ITEMS.ORDER_ID

      and if I add a sort order of "1" on the quantity field, the following query is changed to
      select     "DEMO_ORDER_ITEMS"."ORDER_ID" as "ORDER_ID",
           sum(DEMO_ORDER_ITEMS.QUANTITY) as "QUANTITY"
      from     "DEMO_ORDER_ITEMS" "DEMO_ORDER_ITEMS"
      group by DEMO_ORDER_ITEMS.ORDER_ID
      order by DEMO_ORDER_ITEMS.QUANTITY DESC

      When running this query I get an error
      failed to parse SQL query:
      ORA-00979: not a GROUP BY expression

      It is quite obvious what is wrong with the query but I don't know how to get the query builder tool to generate the correct statement. The order by clause should be order by sum(DEMO_ORDER_ITEMS.QUANTITY) DESC.

      I've tried to do some searching and haven't found much documentation on this issue. Any assistance is appreciated.

      Thanks,
      Tony
        • 1. Re: Query Builder - sort order causing ORA-00979 error
          682558
          Hi Tony,

          I think you need to order by
          sum(DEMO_ORDER_ITEMS.QUANTITY)

          then the query should work fine

          regards

          Michael
          • 2. Re: Query Builder - sort order causing ORA-00979 error
            712342
            Michael, I knew that already, see my original posting

            "It is quite obvious what is wrong with the query but I don't know how to get the query builder tool to generate the correct statement. The order by clause should be order by sum(DEMO_ORDER_ITEMS.QUANTITY) DESC"

            The question is how to get the query builder tool to generate this order by statement?
            • 3. Re: Query Builder - sort order causing ORA-00979 error
              TexasApexDeveloper
              Simple answer is, DON'T DEPEND UPON THE QUERY BUILDER TO WRITE ALL OF YOUR CODE.... Take the code it builds and tweak it.. If you depend on the code it produces, your queries will NOT be as complete as taking the output it produces and modifying it to get the BEST performance out of the selects you are doing..

              Thank you,

              Tony Miller
              Webster, TX
              • 4. Re: Query Builder - sort order causing ORA-00979 error
                Sven W.
                While this statement is generally certainly true and should also be remembered, it is not really helpful for the specific case.

                I think the behaviour described is a bug. However this might be one a little difficult to solve. The user unfortunatly can't influence much of the query builder results directly.

                Either put 1 into the order by clause or use the column alias name without table alias name ("QUANTITY" only).

                My personal solution would be NOT TO use any sort criteia in the query builder. This makes sense in so far as the generated SQL is often basis for some report. If that is an interactive report you won't add a sort order anyway. This is a layout thing and layout will be handled by the end user itself. he can choose whatever sorting he wants.

                Edited by: Sven W. on Jul 22, 2009 11:43 AM
                1 person found this helpful