Need Query without Union and minus.

Hi,

We need a query which retrieves option_code, for a plan from OPTION table which are unique in (market, "ALL"). I have an example below

PLAN	        MARKET	        OPTION_CODE	RATE
PLAN1		TEXAS		1000		1
PLAN1		TEXAS		2000		2
PLAN1		ALL		1000		3
PLAN1		ALL		3000		4

When we ran the SQL, for PLAN1, we need to get
OPTION_CODE	RATE
1000		1
2000		2
3000		4

Currently we are using union and minus to retrieve unique options in both markets.

	select option_code, rate from OPTION
	where plan = 'PLAN1'
	and market= 'TEXAS'
	UNION
	(
	 select option_code, rate from OPTION
	 where plan = 'PLAN1'
	 and market= 'ALL'
		MINUS
	 select option_code, rate from OPTION
	 where plan = 'PLAN1'
	 and market= 'TEXAS'
	)
	

Is there any way to do without using union and minus. We tried to use NOT EXISTS, but it is slowing the query...

Thanks in advance for the help...... Received on Fri Aug 29 2003 - 18:47:44 CEST