Group by adding rows?

[Mr.Hanky]

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

[abhaysk]

I doubt, but lets see results? can u post?

[Thomasps]

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

[Mr.Hanky]

"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

[Thomasps]

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

[abhaysk]

Originally posted by Mr.Hanky
Never question my limited intelligence