distinct doesn't work when I include date

From: <drdave_at_canoemail.com>
Date: 26 Jul 2006 07:42:11 -0700
Message-ID: <1153924931.920211.59450@75g2000cwc.googlegroups.com>

Hi,

I have a join on 3 tables that works with distinct however I need to modify the package and when I include a date the distinct does not work.. something I'm missing here??

The query that works (but not quite what I need)..

open variation_cursor FOR
select distinct mw.mwv_minimum_wage_variation_id,mw.prov_prov_id mwv.minimum_wage_variation_id,mwv.english_text,mwv.french_text, wp.english_name, wp.french_name
from minimum_wage mw, minimum_wage_variation mwv, wid_province wp where mw.effective_date between TO_DATE(datefrom,'yyyy-mm-dd') and to_date(dateto,'yyyy-mm-dd')

and mw.mwv_minimum_wage_variation_id = mwv.minimum_wage_variation_id
and mw.prov_prov_id = wp.province_id
and mw.minimum_wage_ind = 1
and mw.mwv_minimum_wage_variation_id < 200

order by wp.english_name;

the query I'd like to work but the distinct doesn't work..

open variation_cursor FOR
select distinct
mw.mwv_minimum_wage_variation_id,mw.prov_prov_id,mw.effective_date, mwv.minimum_wage_variation_id,mwv.english_text,mwv.french_text, wp.english_name, wp.french_name
from minimum_wage mw, minimum_wage_variation mwv, wid_province wp where mw.effective_date between TO_DATE(datefrom,'yyyy-mm-dd') and to_date(dateto,'yyyy-mm-dd')

and mw.mwv_minimum_wage_variation_id = mwv.minimum_wage_variation_id
and mw.prov_prov_id = wp.province_id
and mw.minimum_wage_ind = 1
and mw.mwv_minimum_wage_variation_id < 200

order by mw.effective_date;

Hopefully the query is enough to see the probelm but I can shed more light on the tables if needed..

tia Dave Received on Wed Jul 26 2006 - 09:42:11 CDT