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