Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: error : ORA-00979

    When i'm loadind data into Summary tables from detail tables, I get this error:
    My code is sthg like:
    insert into tab1
    (
    UNIT
    ,CLASS
    ,ATYPE
    ,DESCR
    ,LOCATION
    ,Class_DESCR
    ,UNIT_DESCR
    )
    select
    DISTINCT a.UNIT
    ,a.CLASS
    ,a.ATYPE
    ,a.DESCR
    ,a.LOCATION
    ,a.CLASS_DESCR
    ,a.UNIT_DESCR
    from TAB2 a
    group by A.unit,
    A.aclass,
    A.type,
    A.location,
    a.unit_descr
    /
    ,a.DESCR
    *
    ERROR at line 15:
    ORA-00979: not a GROUP BY expression
    All the fields are of type varchar2.
    Descr and class_descr are descriptions of the commodity bought.
    What exactly does this error mean?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all the non-aggregates have to be mentioned in the GROUP BY

    but hey, why bother with the GROUP BY, just take it off, because you've got DISTINCT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Thanks for the reply.
    But what if I dont have distinct, then what should I do?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but you do have DISTINCT, i can see it right there after the word SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    I dont have a distinct in another code for loading Summary table, and I get the same error there.

    And more over how can I group a description field?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, that's not fair!!

    how was i supposed to know that you had another query without DISTINCT??!!!

    anyhow, go back to what i said earlier

    all the non-aggregates in the SELECT list have to be mentioned in the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2005
    Posts
    276
    how can I group a description field?

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by nandinir
    how can I group a description field?
    are you saying the descriptions are different but all the other columns could
    be identical? ie: there is no PK

    how do I group a desc field:
    wait! why the hell are you GROUPING? I don't see any group-functions in
    your select statement.

    just use distinct if you are not using a group-function.
    you do know that distinct considers EVERY column you list in your select statement and not just the first column?

    crap, here:
    PHP Code:
    select DISTINCT 
      a
    .UNIT
      
    ,a.CLASS 
      ,
    a.ATYPE 
      
    ,a.DESCR 
      
    ,a.LOCATION 
      
    ,a.CLASS_DESCR 
      
    ,a.UNIT_DESCR 
    from TAB2 a
    group by 
      a
    .UNIT
      
    ,a.CLASS 
      ,
    a.ATYPE 
      
    ,a.DESCR 
      
    ,a.LOCATION 
      
    ,a.CLASS_DESCR 
      
    ,a.UNIT_DESCR
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Maybe the description is the PK? ;-)

  10. #10
    Join Date
    Jul 2005
    Posts
    276
    UNIT
    ,CLASS
    ,ATYPE
    ,DESCR
    ,LOCATION
    ,Class_DESCR
    ,UNIT_DESCR are the PK'S
    Last edited by nandinir; 11-14-06 at 17:45.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    then you are all set.
    you don't need the DISTINCT or the GROUP BY
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    The_Duck++
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2006
    Posts
    4
    like the hint, group by is not roundly.don't use that and try again.

  14. #14
    Join Date
    Jul 2005
    Posts
    276
    Rudy_cg, I didnt understand what you meant

  15. #15
    Join Date
    Jul 2005
    Posts
    276
    I have another question related to this:
    insert into tab1
    (
    col1
    ,col2
    ,col3
    ,acct_dt
    )
    select
    a.col1
    ,a.col2
    ,a.sum(col3)
    ,CASE WHEN SUBSTR(CT.col2,4,3) = 'xx' THEN '01' else '02'
    end
    from tab2 a
    where condn
    group by
    a.col1,a.col2,acct_dt-???
    Do I still have to mention acct_dt in the group by clause.
    Its a new column added to tab1 and not present in the source table tab2.

Posting Permissions

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