Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2013
    Posts
    9

    Unanswered: group function is nested too deeply

    Could people take a look at my query and give any pointers as to why I am getting ORA- 00935

    SELECT
    To_Char (DATE_TIME_CREATED, 'DD-MM-YYYY') DAY,
    STORE_ID STORE,
    TERMINAL_ID TILL,
    TRANSACTION_ID TRANS,
    DEPARTMENT_ID DEPT,
    USER_ID OPERATOR,
    Min((SELECT Min(EMPLOYEE_ID)
    FROM REPORTTRANSACTIONS RT2
    WHERE RT2.REPORT_TRANSACTION_ID = RI.REPORT_TRANSACTION_ID)) EMPID,
    PRODUCT_ID,
    SUM (NET_SALES_VALUE) SAL_VAL,
    SUM (SALES_QUANTITY) SAL_QTY,
    SUM (NET_RETURNS_VALUE) RET_VAL,
    SUM (RETURNS_QUANTITY) RET_QTY,
    SUM (DISCOUNT_VALUE) - SUM (EMPLOYEE_DISCOUNT_VALUE) ITEMDISC,
    SUM (EMPLOYEE_DISCOUNT_VALUE) TMDDISC,
    SUM (ALLOWANCE) ALLOWANCE,
    SUM (BASKET_DISCOUNT_VALUE) TRANSDISC,
    SUM (ORIGINAL_PRICE) - Sum (NEW_PRICE) OVERRIDEVAL,
    SUM (PROMOTION_SAVING) PROMOVAL,
    Sum(CASE
    WHEN item_type IN ('1','6')
    THEN (Sum (net_sales_Value )
    - Sum (BASKET_DISCOUNT_VALUE)
    - Sum (ALLOWANCE)
    + Sum (ORIGINAL_PRICE)
    - Sum (NEW_PRICE)
    - Sum (DISCOUNT_VALUE))
    WHEN item_type IN ('3', '7')
    THEN
    Sum ((NET_RETURNS_VALUE)
    - Sum (BASKET_DISCOUNT_VALUE)
    + Sum (ALLOWANCE)
    + Sum (ORIGINAL_PRICE)
    - Sum (NEW_PRICE)
    + Sum (DISCOUNT_VALUE)
    - Sum (EMPLOYEE_DISCOUNT_VALUE)))END
    FROM REPORTITEMS RI
    WHERE DATE_TIME_CREATED > '07.JUN.13'
    AND ITEM_VOIDED = 0
    AND VOID_TYPE = 0
    AND TRANSACTION_TYPE <> 30
    AND DEPARTMENT_ID <> '0'
    AND STORE_ID = '7001'
    AND TERMINAL_ID = '411'
    AND TRANSACTION_ID = '2329'
    GROUP BY To_Char (DATE_TIME_CREATED, 'DD-MM-YYYY'),STORE_ID, TERMINAL_ID, TRANSACTION_ID, DEPARTMENT_ID, USER_ID, PRODUCT_ID
    ORDER BY 2,3,4

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This seems to be an invalid query (CASE is wrong, it misses a closing bracket after the END keyword) so I'm somewhat surprised that you managed to get ANY message. Could it be that you didn't post a real query you ran?

  3. #3
    Join Date
    Jun 2013
    Posts
    9
    Hi Littlefoot, just double checked, that is the correct* query.

  4. #4
    Join Date
    Jun 2013
    Posts
    9
    If I amend to:

    SELECT
    To_Char (DATE_TIME_CREATED, 'DD-MM-YYYY') DAY,
    STORE_ID STORE,
    TERMINAL_ID TILL,
    TRANSACTION_ID TRANS,
    DEPARTMENT_ID DEPT,
    USER_ID OPERATOR,
    Min((SELECT Min(EMPLOYEE_ID)
    FROM REPORTTRANSACTIONS RT2
    WHERE RT2.REPORT_TRANSACTION_ID = RI.REPORT_TRANSACTION_ID)) EMPID,
    PRODUCT_ID,
    SUM (NET_SALES_VALUE) SAL_VAL,
    SUM (SALES_QUANTITY) SAL_QTY,
    SUM (NET_RETURNS_VALUE) RET_VAL,
    SUM (RETURNS_QUANTITY) RET_QTY,
    SUM (DISCOUNT_VALUE) - SUM (EMPLOYEE_DISCOUNT_VALUE) ITEMDISC,
    SUM (EMPLOYEE_DISCOUNT_VALUE) TMDDISC,
    SUM (ALLOWANCE) ALLOWANCE,
    SUM (BASKET_DISCOUNT_VALUE) TRANSDISC,
    SUM (ORIGINAL_PRICE) - Sum (NEW_PRICE) OVERRIDEVAL,
    SUM (PROMOTION_SAVING) PROMOVAL,
    Sum (CASE
    WHEN item_type IN ('1','6')
    THEN Sum (net_sales_Value )
    - Sum (BASKET_DISCOUNT_VALUE)
    - Sum (ALLOWANCE)
    + Sum (ORIGINAL_PRICE)
    - Sum (NEW_PRICE)
    - Sum (DISCOUNT_VALUE)
    WHEN item_type IN ('3', '7')
    THEN
    Sum (NET_RETURNS_VALUE)
    - Sum (BASKET_DISCOUNT_VALUE)
    + Sum (ALLOWANCE)
    + Sum (ORIGINAL_PRICE)
    - Sum (NEW_PRICE)
    + Sum (DISCOUNT_VALUE)
    - Sum (EMPLOYEE_DISCOUNT_VALUE)END)
    FROM REPORTITEMS RI
    WHERE DATE_TIME_CREATED > '07.JUN.13'
    AND ITEM_VOIDED = 0
    AND VOID_TYPE = 0
    AND TRANSACTION_TYPE <> 30
    AND DEPARTMENT_ID <> '0'
    AND STORE_ID = '7001'
    AND TERMINAL_ID = '411'
    AND TRANSACTION_ID = '2329'
    GROUP BY To_Char (DATE_TIME_CREATED, 'DD-MM-YYYY'),STORE_ID, TERMINAL_ID, TRANSACTION_ID, DEPARTMENT_ID, USER_ID, PRODUCT_ID
    ORDER BY 2,3,4

    Then I get ORA-00937: not a single-group group function at To_Char (DATE_TIME_CREATED, 'DD-MM-YYYY') DAY,

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Indicated TO_CHAR is a part of GROUP BY clause, so that shouldn't be a problem.

    However, you can't
    Code:
    select min(select min(something) from some_table) col_alias ...
    Code:
    SQL> select min(select min(deptno) from dept) mindep
      2  from emp;
    select min(select min(deptno) from dept) mindep
               *
    ERROR at line 1:
    ORA-00936: missing expression
    
    
    SQL>
    See? Missing expression.

    What happens if you remove the whole EMPID part from your query?

  6. #6
    Join Date
    Jun 2013
    Posts
    9
    When I remove the EMPID I still get the group by error at TO_CHAR.

    When I remove the Case statement it runs perfectly, so the error is with that.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "The whole EMPID part" is the whole
    Code:
    Min((SELECT Min(EMPLOYEE_ID) 
    FROM REPORTTRANSACTIONS RT2
    WHERE RT2.REPORT_TRANSACTION_ID = RI.REPORT_TRANSACTION_ID)) EMPID,
    As I told you: you can't have that.

    Would a simple join between these two tables solve the issue? Such as
    Code:
    select ri.store_id,
           ri.terminal_id,
           min(rt2.employee_id) min_empid,
           ...
    from reportitems ri,
         reporttansactions rt2
    where ...
      and rt2.report_transactions_id = ri.report_transaction_id
    group by ...

  8. #8
    Join Date
    Jun 2013
    Posts
    9
    Yes I removed the whole

    Min((SELECT Min(EMPLOYEE_ID)
    FROM REPORTTRANSACTIONS RT2
    WHERE RT2.REPORT_TRANSACTION_ID = RI.REPORT_TRANSACTION_ID)) EMPID,
    I needed this part, as it the ID appears once per transaction in Transactions but can appear many times in Items.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, so - did you rewrite it as I suggested?

  10. #10
    Join Date
    Jun 2013
    Posts
    9
    Still get ORA-00937: not a single-group group function

    SELECT
    To_Char (RI.DATE_TIME_CREATED, 'DD-MM-YYYY') DAY,
    ri.store_id,
    ri.terminal_id,
    ri.TRANSACTION_id,
    Min(rt2.employee_id) min_empid,
    MIN (RI.PRODUCT_ID),
    SUM (RI.NET_SALES_VALUE) SAL_VAL,
    SUM (RI.SALES_QUANTITY) SAL_QTY,
    SUM (RI.NET_RETURNS_VALUE) RET_VAL,
    SUM (RI.RETURNS_QUANTITY) RET_QTY,
    SUM (RI.DISCOUNT_VALUE) - SUM (RI.EMPLOYEE_DISCOUNT_VALUE) ITEMDISC,
    SUM (RI.EMPLOYEE_DISCOUNT_VALUE) TMDDISC,
    SUM (RI.ALLOWANCE) ALLOWANCE,
    SUM (RI.BASKET_DISCOUNT_VALUE) TRANSDISC,
    suM (RI.ORIGINAL_PRICE) - Sum (RI.NEW_PRICE) OVERRIDEVAL,
    SUM (RI.PROMOTION_SAVING) PROMOVAL,
    Sum (CASE
    WHEN item_type IN ('1','6')
    THEN Sum (net_sales_Value )
    - Sum (BASKET_DISCOUNT_VALUE)
    - Sum (ALLOWANCE)
    + Sum (ORIGINAL_PRICE)
    - Sum (NEW_PRICE)
    - Sum (DISCOUNT_VALUE)
    WHEN item_type IN ('3', '7')
    THEN
    Sum (NET_RETURNS_VALUE)
    - Sum (BASKET_DISCOUNT_VALUE)
    + Sum (ALLOWANCE)
    + Sum (ORIGINAL_PRICE)
    - Sum (NEW_PRICE)
    + Sum (DISCOUNT_VALUE)
    - Sum (EMPLOYEE_DISCOUNT_VALUE)END)
    FROM REPORTITEMS RI,
    REPORTTRANSACTIONS RT2
    WHERE DATE_TIME_CREATED > '07.JUN.13'
    and rt2.report_transaction_id = ri.report_transaction_id
    AND RI.ITEM_VOIDED = 0
    AND RI.VOID_TYPE = 0
    AND RI.TRANSACTION_TYPE <> 30
    AND RI.DEPARTMENT_ID <> '0'
    GROUP BY ri.store_id, ri.terminal_id, ri.TRANSACTION_id
    ORDER BY 1,2,3

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    item_type is not in GROUP BY clause.

    So, how about to remove SUMs in the CASE expression,
    like this?
    Sum (CASE
    WHEN item_type IN ('1','6')
    THEN net_sales_Value
    - BASKET_DISCOUNT_VALUE
    - ALLOWANCE
    + ORIGINAL_PRICE
    - NEW_PRICE
    - DISCOUNT_VALUE
    WHEN item_type IN ('3', '7')
    THEN
    NET_RETURNS_VALUE
    - BASKET_DISCOUNT_VALUE
    + ALLOWANCE
    + ORIGINAL_PRICE
    - NEW_PRICE
    + DISCOUNT_VALUE
    - EMPLOYEE_DISCOUNT_VALUE
    END)

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    All columns that aren't aggregated must be in GROUP BY. "TO_CHAR(RI.DATE_TIME_CREATED ..." is not.

  13. #13
    Join Date
    Jun 2013
    Posts
    9
    Tonkuma,

    When I do as you suggest the query runs, however the case just returns either the net_sales_value or the net_returns_value. Does not peform the 'summing' I require.

    D

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    however the case just returns either the net_sales_value or the net_returns_value. Does not peform the 'summing' I require.
    Did you tried?

    'summing' would be done by outer SUM function.
    Do you want to sum more?

    Please see the formatted code.
    Code:
           SUM( CASE 
                WHEN item_type IN ('1' , '6') THEN
                       net_sales_Value 
                     - BASKET_DISCOUNT_VALUE 
                     - ALLOWANCE 
                     + ORIGINAL_PRICE
                     - NEW_PRICE
                     - DISCOUNT_VALUE
                WHEN item_type IN ('3' , '7') THEN
                       NET_RETURNS_VALUE 
                     - BASKET_DISCOUNT_VALUE 
                     + ALLOWANCE
                     + ORIGINAL_PRICE
                     - NEW_PRICE
                     + DISCOUNT_VALUE
                     - EMPLOYEE_DISCOUNT_VALUE
                END
              )
    It may perform like the following pshudo code.
    Code:
    For each row,
       If item_type IN ('1' , '6') then
          Add   net_sales_Value
              - BASKET_DISCOUNT_VALUE 
              - ALLOWANCE 
              + ORIGINAL_PRICE
              - NEW_PRICE
              - DISCOUNT_VALUE
           to the result;
       Else if item_type IN ('3' , '7') then
          Add   NET_RETURNS_VALUE 
              - BASKET_DISCOUNT_VALUE 
              + ALLOWANCE
              + ORIGINAL_PRICE
              - NEW_PRICE
              + DISCOUNT_VALUE
              - EMPLOYEE_DISCOUNT_VALUE
           to the result;
       End If;
    End For;
    Last edited by tonkuma; 06-11-13 at 12:49.

  15. #15
    Join Date
    Jun 2013
    Posts
    9
    tonkuma,

    Yes I copied your change it but as I said it just gave me the first part of the Case, add didnt add and subtract the other values, which is what I'd like it to do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •