-
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.
-
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"
-
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
-
"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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|