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!

ORA-00937 when HAVING clause is present.

Discussion in 'SQL PL/SQL' started by OldSchoolCoder, Aug 6, 2014.

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    This query will run without the HAVING clause, but when I insert the HAVING, I end up with ORA-00937: not a single-group group function. Since there are multiple entries in the ELIG_TB and the ADR_US_TB, I need to select the row with the most up to date date from each. (I am using Oracle 11g.)

    Of all the things I have trouble with, this error is the most frequent. Can anyone help me rewrite this to avoid the issue? Also, I wouldn't mind getting an explanation on the triggering of this error too so that I can be more informed.

    Thank you!

    Code (SQL):
    SELECT own.DIST                    AS Dist_Off,
           dtl.FRST_MID_LST            AS Name,
           adr.ADR                     AS Adr1,
           elg.ELG_CD                  AS ELG_Cd,
           dtl.DOB                     AS DOB,
           CASE
             WHEN dtl.TY_CD  = 'A'
             THEN dtl.ID  
           END                         AS A_ID,
           rep.FRST_MID_LST            AS Rep,
           inv.INV_NUM                 AS INV,
           inv.Svc_ID                  AS SVC_ID,
           inv.SVC_CD                  AS SVC_CD,

    FROM INV_TB inv
    JOIN EMP_TB dtl
      ON dtl.EMP_KEY = inv.INV_KEY
    JOIN ELIG_TB elg
      ON elg.ELG_KEY = inv. INV_KEY
    JOIN BOSS_TB bos
      ON bos.BOS_KEY = elg.ELG_KEY  
    JOIN STOR_TB own
      ON own.OWN_KEY = bos.BOS_KEY
    JOIN XREF_TB xr
      ON xr.XREF_KEY = bos.BOS_KEY
    JOIN PARNT_TB rep
      ON rep.PARNT_KEY = xr.XREF_KEY
    LEFT JOIN ADR_US_TB adu
      ON adu.ADR_US_KEY = rep.SUB_PARNT_KEY
    LEFT JOIN ADR_TB adr
      ON adr.ADR_KEY = adu.ADR_US_KEY
    WHERE inv.STAT = 'O'
      AND inv.PAID >= TO_DATE('07/01/2014', 'MM/DD/YYYY')
      AND inv.PAID <= TO_DATE('07/30/2014', 'MM/DD/YYYY')
    HAVING MAX(elg.END) >= TO_DATE('07/01/2014', 'MM/DD/YYYY')
      AND MAX(adu.END) >= TO_DATE('07/01/2014', 'MM/DD/YYYY')
    ORDER BY own.DIST,
             CASE
               WHEN dtl.TY_CD  = 'A'
               THEN dtl.ID  
             END;
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    From "Beginning Oracle SQL", OakTable Press, 8.7, pg 217 :

    "Normally, you use the HAVING clause only following a GROUP BY..."

    HTH

    CJ
     
  3. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Thank you CJ. I attempted to include an aggregate (that I really don't need) as well as a Group By and ended up with another error. (ORA-00979: not a GROUP BY expression) Usually, there is more than one way to code things. Do you know how I could rewrite this to get the desired outcome? The query seems simple until I have to get associated data of the MAX dates. I do not need the dates in my results - which is what is puzzling me.

    Thank you.
    Lynda
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Okay....reading the rest of the chapter.

    "...the SQL language allows...HAVING clause without a preceding GROUP BY...Oracle assumes an implicit GROUP BY on a constant..."

    "...valid HAVING clauses without group functions are very rare, and they should be rewritten..."

    From your example above...the HAVING clause should attempt to group the results based on the following :

    HAVING MAX(elg.END) >= TO_DATE('07/01/2014', 'MM/DD/YYYY')
    AND MAX(adu.END) >= TO_DATE('07/01/2014', 'MM/DD/YYYY')

    However, I am not entirely sure you need the MAX function as that equates to a ROW as the HAVING applies to the group. Have you tried as :

    HAVING elg.END >= TO_DATE('07/01/2014', 'MM/DD/YYYY')
    AND adu.END >= TO_DATE('07/01/2014', 'MM/DD/YYYY')


    HTH

    CJ
     
  5. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Thank you again, CJ. The MAX is required for elg.END since there can be more than one >= 07/01/2014. I should mention that I also tried to change things up a bit by adding those 2 dates into the query so that they would be part of the Group By, but that didn't work. I still got ORA-00979: not a GROUP BY expression. Here's how it currently looks (I know that END is a keyword-this is just dummied code to mask company info):
    Code (SQL):
    SELECT own.DIST                    AS Dist_Off,
           dtl.FRST_MID_LST            AS Name,
           adr.ADR                     AS Adr1,
           adu.END                     AS ADR_END,
           elg.ELG_CD                  AS ELG_Cd,
           elg.END                     AS ELG_END,
           dtl.DOB                     AS DOB,
           CASE
             WHEN dtl.TY_CD  = 'A'
             THEN dtl.ID  
           END                         AS A_ID,
           rep.FRST_MID_LST            AS Rep,
           inv.INV_NUM                 AS INV,
           inv.Svc_ID                  AS SVC_ID,
           inv.SVC_CD                  AS SVC_CD,

    FROM INV_TB inv
    JOIN EMP_TB dtl
      ON dtl.EMP_KEY = inv.INV_KEY
    JOIN ELIG_TB elg
      ON elg.ELG_KEY = inv. INV_KEY
    JOIN BOSS_TB bos
      ON bos.BOS_KEY = elg.ELG_KEY  
    JOIN STOR_TB own
      ON own.OWN_KEY = bos.BOS_KEY
    JOIN XREF_TB xr
      ON xr.XREF_KEY = bos.BOS_KEY
    JOIN PARNT_TB rep
      ON rep.PARNT_KEY = xr.XREF_KEY
    LEFT JOIN ADR_US_TB adu
      ON adu.ADR_US_KEY = rep.SUB_PARNT_KEY
    LEFT JOIN ADR_TB adr
      ON adr.ADR_KEY = adu.ADR_US_KEY
    WHERE inv.STAT = 'O'
      AND inv.PAID >= TO_DATE('07/01/2014', 'MM/DD/YYYY')
      AND inv.PAID <= TO_DATE('07/30/2014', 'MM/DD/YYYY')
    HAVING MAX(elg.END) >= TO_DATE('07/01/2014', 'MM/DD/YYYY')
      AND MAX(adu.END) >= TO_DATE('07/01/2014', 'MM/DD/YYYY')
    GROUP BY own.DIST,
           dtl.FRST_MID_LST,
           adr.ADR,
           adu.END,
           elg.ELG_CD,
           elg.END,
           dtl.DOB,
           CASE
             WHEN dtl.TY_CD  = 'A'
             THEN dtl.ID  
           END,
           rep.FRST_MID_LST,
           inv.INV_NUM,
           inv.Svc_ID,
           inv.SVC_CD
    ORDER BY own.DIST,
             CASE
               WHEN dtl.TY_CD  = 'A'
               THEN dtl.ID  
             END;
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Every example I see shows the GROUP BY coming before the HAVING and...every HAVING example I look up (so far) that is used w/ MAX also shows the MAX function on the column in the select list...which you don't have. Not entirely sure if that changes the results of your query though.

    CJ
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula