1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Multiple Nested Selects in From clause require Group By, but get ORA-00934

Discussion in 'SQL PL/SQL' started by OldSchoolCoder, May 15, 2014.

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    My query requires me to result in many calculated columns so I determined that the following was the easiest way to write it. However, I can't get past the ORA-00934 error - I'm of the belief that it is caused by my Case Statement, but I don't know how to rewrite it. Any help would be MOST appreciated!

    Code (SQL):
    SELECT aaa.Invoice, aaa.OrigInvMoYr, aaa.ProvBilledAmt,
           aaa.AmtReq, aaa.Lict, aaa.CarrierPayCnt, aaa.CarrPayAmt,
           aaa.OverPayCnt, aaa.OverPayAmt, aaa.CarrDenyCnt,
           aaa.CarrDenyAmt, aaa.WriteOffCnt, aaa.WriteOffAmt
    FROM
       (SELECT cl.INVOICE AS Invoice, MIN(ist.SENT_DAT)
               AS OrigInvMoYr,

           CASE
             WHEN h.C_TY_CD <> 'P' THEN SUM(h.CHARGED)  
             WHEN rxh.C_TY_CD = 'P' THEN SUM(rxh.CHARGED)
           END AS ProvBilledAmt,

           inv.BILLED AS AmtReq,

          (SELECT COUNT(cl.LINE_N)
           FROM CLAIM_T cl
           WHERE EXISTS
               (SELECT DISTINCT cl.ID_NBR
                FROM CLAIM_T cl
               )
           HAVING MIN(cl.SENT_DAT) >
                  TO_DATE('01/01/0001', 'MM/DD/YYYY')
           ) AS LIct,

          (SELECT COUNT(rsp.LINE_N)
           FROM RESPONSE_T rsp
           WHERE rsp.RSPS_REA LIKE 'R%'  
             AND rsp.RSPS_REA <> 'NOT'
             AND EXISTS
               (SELECT DISTINCT rsp.ID_NBR
                FROM RESPONSE_T rsp
               )
           HAVING MAX(rsp.RSPS_DAT) >
                  TO_DATE('01/01/0001', 'MM/DD/YYYY')
          ) AS CarrierPayCnt,

           SUM(rsp.RSPS_AMT) AS CarrPayAmt,

          (SELECT COUNT(rsp.LINE_N)
           FROM RESPONSE_T rsp
           WHERE EXISTS
               (SELECT DISTINCT rsp.ID_NBR
                FROM RESPONSE_T rsp
               )
             AND rsp.AMT_OVER IS NOT NULL
             AND rsp.AMT_OVER > 0
           ) AS OverPayCnt,

           SUM(rsp.AMT_OVER) AS OverPayAmt,

           (SELECT COUNT(rsp.LINE_N)
            FROM RESPONSE_T rsp
            WHERE EXISTS
                (SELECT DISTINCT rsp.ID_NBR
                 FROM RESPONSE_T rsp
                )
              AND rsp.RSPS_REA LIKE 'D%'
            HAVING MAX(rsp.RSPS_DAT) >
                   TO_DATE('01/01/0001', 'MM/DD/YYYY')        
            ) AS CarrDenyCnt,

            (SELECT SUM(cl.BILL_AMT)
             FROM CLAIM_T cl
             WHERE rsp.RSPS_REA LIKE 'D%'
             HAVING MAX(rsp.RSPS_DAT) >
                   TO_DATE('01/01/0001', 'MM/DD/YYYY')
            ) AS CarrDenyAmt,

            (SELECT COUNT(rsp.LINE_N)
             FROM RESPONSE_T rsp
             WHERE EXISTS
                 (SELECT DISTINCT rsp.ID_NBR
                  FROM RESPONSE_T rsp
                 )
               AND rsp.RSPS_REA = 'NOT'
             HAVING MAX(rsp.RSPS_DAT) >
                    TO_DATE('01/01/0001', 'MM/DD/YYYY')        
            ) AS WriteOffCnt,

            (SELECT SUM(cl.BILL_AMT)
             FROM CLAIM_T cl
             WHERE rsp.RSPS_REA = 'NOT'
             HAVING MAX(rsp.RSPS_DAT) >
                    TO_DATE('01/01/0001', 'MM/DD/YYYY')
            ) AS WriteOffAmt
       FROM INVOICE_T inv
       JOIN CARRIER_T c
         ON c.C_ID = inv.C_ID
       JOIN INVOICE_SENT_T ist
         ON ist.INVOICE = inv.INVOICE
       JOIN CLAIM_T cl
         ON cl.INVOICE = ist.INVOICE
            AND cl.SENT_DAT = ist.SENT_DAT
       JOIN RESPONSE_T rsp
         ON rsp.INVOICE = inv.INVOICE
       JOIN MAIN_T h
         ON h.ID_NBR = rsp.ID_NBR
       JOIN PHARMACY_T rxh
         ON rxh.ID_NBR = rsp.ID_NBR
       WHERE inv.C_ID = '0000000001'
       AND (inv.CLOSE_DAT IS NULL
              OR inv.CLOSE_DAT > TO_DATE('12/31/2099', 'MM/DD/YYYY'))
       AND inv.CLOSE_REA IS NULL
       GROUP BY
             cl.INVOICE,
           CASE
             WHEN h.C_TY_CD <> 'P' THEN SUM(h.CHARGED)  
             WHEN rxh.C_TY_CD = 'P' THEN SUM(rxh.CHARGED)
           END,
           inv.BILLED
           
       ) aaa

    ORDER BY OrigInvMoYr;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    1) It is impossible to use aggregation functions in the GROUP BY offer

    Simple example :

    Code (SQL):


    CREATE TABLE test_odd
    AS
        SELECT
             level id,
             MOD(round(dbms_random.VALUE(1,level)),2)  is_odd,
             MOD(level,10) grp
        FROM dual
        CONNECT BY level < 101;
    SELECT
        grp
        ,SUM(CASE WHEN is_odd = 0 THEN 1 END ) cnt_even
        ,SUM(CASE WHEN is_odd = 1 THEN 1 END ) cnt_odd
    FROM     test_odd
    GROUP BY  grp
    ORDER BY grp;


    SQL>
     
    TABLE created
     
                 GRP   CNT_EVEN    CNT_ODD
    ---------- ---------- ----------
             0          3          7
             1          3          7
             2          8          2
             3          8          2
             4          6          4
             5          5          5
             6          4          6
             7          4          6
             8          5          5
             9          3          7
     
    10 ROWS selected


     
     
  3. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I've managed to get past the ORA-00934 error by changing the CASE statement as follows, but now I'm getting ORA-00979. I've tried removing each column from the Group By and even removing the Group By all together - the Group By is required though. I'm just not seeing how this error is generated.
    Code (SQL):
    SELECT aaa.Invoice, aaa.OrigInvMoYr, aaa.ProvBilledAmt,
           aaa.AmtReq, aaa.Lict, aaa.CarrierPayCnt, aaa.CarrPayAmt,
           aaa.OverPayCnt, aaa.OverPayAmt, aaa.CarrDenyCnt,
           aaa.CarrDenyAmt, aaa.WriteOffCnt, aaa.WriteOffAmt
    FROM
       (SELECT cl.INVOICE AS Invoice, MIN(ist.SENT_DAT)
               AS OrigInvMoYr,
     
           SUM(CASE
             WHEN h.C_TY_CD <> 'P' THEN h.CHARGED
             WHEN rxh.C_TY_CD = 'P' THEN rxh.CHARGED
           END) AS ProvBilledAmt,
     
           inv.BILLED AS AmtReq,
     
          (SELECT COUNT(cl.LINE_N)
           FROM CLAIM_T cl
           WHERE EXISTS
               (SELECT DISTINCT cl.ID_NBR
                FROM CLAIM_T cl
               )
           HAVING MIN(cl.SENT_DAT) >
                  TO_DATE('01/01/0001', 'MM/DD/YYYY')
           ) AS LIct,
     
          (SELECT COUNT(rsp.LINE_N)
           FROM RESPONSE_T rsp
           WHERE rsp.RSPS_REA LIKE 'R%'  
             AND rsp.RSPS_REA <> 'NOT'
             AND EXISTS
               (SELECT DISTINCT rsp.ID_NBR
                FROM RESPONSE_T rsp
               )
           HAVING MAX(rsp.RSPS_DAT) >
                  TO_DATE('01/01/0001', 'MM/DD/YYYY')
          ) AS CarrierPayCnt,
     
           SUM(rsp.RSPS_AMT) AS CarrPayAmt,
     
          (SELECT COUNT(rsp.LINE_N)
           FROM RESPONSE_T rsp
           WHERE EXISTS
               (SELECT DISTINCT rsp.ID_NBR
                FROM RESPONSE_T rsp
               )
             AND rsp.AMT_OVER IS NOT NULL
             AND rsp.AMT_OVER > 0
           ) AS OverPayCnt,
     
           SUM(rsp.AMT_OVER) AS OverPayAmt,
     
           (SELECT COUNT(rsp.LINE_N)
            FROM RESPONSE_T rsp
            WHERE EXISTS
                (SELECT DISTINCT rsp.ID_NBR
                 FROM RESPONSE_T rsp
                )
              AND rsp.RSPS_REA LIKE 'D%'
            HAVING MAX(rsp.RSPS_DAT) >
                   TO_DATE('01/01/0001', 'MM/DD/YYYY')        
            ) AS CarrDenyCnt,
     
            (SELECT SUM(cl.BILL_AMT)
             FROM CLAIM_T cl
             WHERE rsp.RSPS_REA LIKE 'D%'
             HAVING MAX(rsp.RSPS_DAT) >
                   TO_DATE('01/01/0001', 'MM/DD/YYYY')
            ) AS CarrDenyAmt,
     
            (SELECT COUNT(rsp.LINE_N)
             FROM RESPONSE_T rsp
             WHERE EXISTS
                 (SELECT DISTINCT rsp.ID_NBR
                  FROM RESPONSE_T rsp
                 )
               AND rsp.RSPS_REA = 'NOT'
             HAVING MAX(rsp.RSPS_DAT) >
                    TO_DATE('01/01/0001', 'MM/DD/YYYY')        
            ) AS WriteOffCnt,
     
            (SELECT SUM(cl.BILL_AMT)
             FROM CLAIM_T cl
             WHERE rsp.RSPS_REA = 'NOT'
             HAVING MAX(rsp.RSPS_DAT) >
                    TO_DATE('01/01/0001', 'MM/DD/YYYY')
            ) AS WriteOffAmt
       FROM INVOICE_T inv
       JOIN CARRIER_T c
         ON c.C_ID = inv.C_ID
       JOIN INVOICE_SENT_T ist
         ON ist.INVOICE = inv.INVOICE
       JOIN CLAIM_T cl
         ON cl.INVOICE = ist.INVOICE
            AND cl.SENT_DAT = ist.SENT_DAT
       JOIN RESPONSE_T rsp
         ON rsp.INVOICE = inv.INVOICE
       JOIN MAIN_T h
         ON h.ID_NBR = rsp.ID_NBR
       JOIN PHARMACY_T rxh
         ON rxh.ID_NBR = rsp.ID_NBR
       WHERE inv.C_ID = '0000000001'
       AND (inv.CLOSE_DAT IS NULL
              OR inv.CLOSE_DAT > TO_DATE('12/31/2099', 'MM/DD/YYYY'))
       AND inv.CLOSE_REA IS NULL
       GROUP BY
             cl.INVOICE,
             inv.BILLED
     
       ) aaa
     
    ORDER BY OrigInvMoYr;
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    1)
    Unless such computation correct?

    Code (SQL):
     
             SUM(CASE
             WHEN h.C_TY_CD <> 'P' THEN h.CHARGED
             WHEN rxh.C_TY_CD = 'P' THEN rxh.CHARGED
           END) AS ProvBilledAmt,
     
    Note : it is necessary to divide computation into two columns or to add necessary columns in group by


    2) Pay attention to units of a look: (SELECT .. )
     
  5. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I can't quite make out what you were indicating as incorrect, but it appears that you're indicating the case statement. That part, I believe is working since I completely removed it from the query to test that theory and still got the 979 error.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Oracle version ?
     
  7. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    My apologies - I hope you don't mind my asking questions - I just want to understand what you're suggesting. Are you suggesting that I remove all of my subqueries that contain computations and then add them back in 1 at a time?

    Yes, I am using Oracle.
    Thank you!
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    I asked a question to specify the oracle version.
    in the 11th version there was a bug with group by use.
     
  9. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Very good to know! In the environment I'm working in it is version 10.2.0.4.0.
     
  10. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    OOPS! I went and double checked with the DBA and was informed that it is 11.2. Sorry.
     
  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    This is very similar to a bug : 13622036 (check os version )
    ...probably it is necessary to upgrated to version 11.2.3
     
  12. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Thank you very much Sergey! I will follow up with that information. Your help is most appreciated.