group by probs

[sammybee]

Hi All,

I'm having problems with the following code, my results are bringing back more than 1 a.vin_refno per a.vin_pro_refno

any ideas?

many thanks

Sam

SELECT a.vin_pro_refno, MAX (a.vin_refno), vin.vin_hrv_vcl_code,
vin.vin_vpa_curr_code
FROM void_instances a, void_instances vin, hou_prop_statuses
WHERE a.vin_refno = vin.vin_refno
and vin.vin_pro_refno = hps_pro_refno
and hps_hpc_code = 'VOID'
and hps_end_date is null
GROUP BY a.vin_pro_refno, vin.vin_hrv_vcl_code, vin.vin_vpa_curr_code

 

[SantaMufasa]

Sammy,

I'll bet that you are seeing multiple a.vin_refnos per a.vin_pro_refno because either your
"vin.vin_hrv_vcl_code" or your "vin.vin_vpa_curr_code" values differ, or both.

Whenever a group function such as MAX() appearss, it evaluates the MAX() for each GROUPed result set. In your case, Oracle produces a separate result set for each group of rows that match on "a.vin_pro_refno, vin.vin_hrv_vcl_code, and vin.vin_vpa_curr_code" when those expressions considered as a single string as though they were concatenated.

Does this match your situation? If not, please post an excerpt of your output so we can troubleshoot it.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:45 (26Nov04) UTC (aka "GMT" and "Zulu"),
@ 10:45 (26Nov04) Mountain Time