DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Group by adding rows?

  1. #1
    Join Date
    Jan 2001
    Posts
    3,134

    Group by adding rows?

    Oracle 9.2.0.3
    Unix HP-UX 11.00

    We have a query that we run in production that is returning 20 rows when the "group by" clause is added. BUT it returns zero rows when the "group by" clause is omitted.

    I thought group by only processed data after the fact?

    here is the query...

    SELECT plno.svc_grp_nr,
    plno.nvp_ter_nr,
    plno.pln_grp_nr,
    plno.nvp_nr
    FROM table1 acqo,
    table2 plno
    WHERE plno.bat_nr = 'P0170782'
    AND plno.nvp_bid_nr = 'P570009413'
    AND plno.bat_nr = acqo.bat_nr
    AND plno.nvp_nr = acqo.nvp_nr
    AND plno.pln_grp_nr IS NOT NULL
    AND plno.pln_grp_nr != -99
    AND 1 <
    (SELECT COUNT(DISTINCT nvp_nr)
    FROM table2
    WHERE bat_nr = 'P0170782'
    AND nvp_bid_nr = 'P570009413'
    AND svc_grp_nr = plno.svc_grp_nr
    AND nvp_ter_nr = plno.nvp_ter_nr
    AND pln_grp_nr = plno.pln_grp_nr)
    AND NOT EXISTS( SELECT 1
    FROM table1 acqi,
    table2 plni
    WHERE plni.bat_nr = 'P0170782'
    AND plni.nvp_bid_nr = 'P570009413'
    AND svc_grp_nr = plno.svc_grp_nr
    AND nvp_ter_nr = plno.nvp_ter_nr
    AND plno.pln_grp_nr = plni.pln_grp_nr
    AND plno.nvp_nr <> plni.nvp_nr
    AND plni.bat_nr = acqi.bat_nr
    AND plni.nvp_nr = acqi.nvp_nr
    AND acqo.pkg_acq_mth_typ_cd = acqi.pkg_acq_mth_typ_cd)
    GROUP BY plno.svc_grp_nr,
    plno.nvp_ter_nr,
    plno.pln_grp_nr,
    plno.nvp_nr
    /



    MH
    I remember when this place was cool.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I doubt, but lets see results? can u post?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Strange!!

    Hi Henky

    Very strange ... Will you please run this query and check the output. But i don't agree with your finding!

    SELECT distinct plno.svc_grp_nr,
    plno.nvp_ter_nr,
    plno.pln_grp_nr,
    plno.nvp_nr
    FROM table1 acqo,
    table2 plno
    WHERE plno.bat_nr = 'P0170782'
    AND plno.nvp_bid_nr = 'P570009413'
    AND plno.bat_nr = acqo.bat_nr
    AND plno.nvp_nr = acqo.nvp_nr
    AND plno.pln_grp_nr IS NOT NULL
    AND plno.pln_grp_nr != -99
    AND 1 <
    (SELECT COUNT(DISTINCT nvp_nr)
    FROM table2
    WHERE bat_nr = 'P0170782'
    AND nvp_bid_nr = 'P570009413'
    AND svc_grp_nr = plno.svc_grp_nr
    AND nvp_ter_nr = plno.nvp_ter_nr
    AND pln_grp_nr = plno.pln_grp_nr)
    AND NOT EXISTS(
    SELECT 1
    FROM table1 acqi,
    table2 plni
    WHERE plni.bat_nr = 'P0170782'
    AND plni.nvp_bid_nr = 'P570009413'
    AND svc_grp_nr = plno.svc_grp_nr
    AND nvp_ter_nr = plno.nvp_ter_nr
    AND plno.pln_grp_nr = plni.pln_grp_nr
    AND plno.nvp_nr <> plni.nvp_nr
    AND plni.bat_nr = acqi.bat_nr
    AND plni.nvp_nr = acqi.nvp_nr
    AND acqo.pkg_acq_mth_typ_cd = acqi.pkg_acq_mth_typ_cd)

    /

    Regards,
    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    "I doubt" "I don't agree"
    What am I lying here!?
    I ran the query with and without the group by.

    If I omit this section..

    "GROUP BY plno.svc_grp_nr,
    plno.nvp_ter_nr,
    plno.pln_grp_nr,
    plno.nvp_nr"

    I get no rows, if it is left in I get 20 rows returned.

    Never question my limited intelligence and integrity again, shame on both of you!



    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Thanks!

    Whoops.. Need to get some cloths to hide my face...

    Mr. Henky... Thats too much.. Anyhow thanks for your "GOOD UNDERSTANDING". I accepted the shame!!

    Take care!

    Regards,

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by Mr.Hanky
    Never question my limited intelligence
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

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


Click Here to Expand Forum to Full Width