Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: EXISTS and OR statement
val wrote:
> hi,
> i wrote the sql statement with the EXISTS and OR statement but it's
> seems too be running a long time and then runs out of temp space. the
> reason i have OR is that i want to get A RECORD if any one of the three
> conditions is met.
>
> SELECT count(distinct b.EML_ADDR) FROM table 1a, table 2 b, table 3 d
> WHERE exists(select cust_key from table 4 e where e.SLS_CHNL_TYP_CD =
> 'O' and brnd_key = 1 and TRUNC(e.ins_dt) >= TRUNC(SYSDATE) - 30 and
> e.cust_key =d.cust_key)
> or exists (select cust_key from cust_cr_crd_acct_t e where crd_typ_cd =
> 'BR' and TRUNC(e.ins_dt) >= TRUNC(SYSDATE) - 30 and d.cust_key =
> e.cust_key)
> or d.EML_ADDR_KEY in ( select eml_addr_key from src_cust_eml_t where
> EML_OPT_OUT_IND = 'N' and TRUNC(ins_dt) >= TRUNC(SYSDATE) - 30)
> or exists ( select eml_addr_key from src_cust_eml_t f where
> EML_OPT_OUT_IND = 'N' and TRUNC(ins_dt) >= TRUNC(SYSDATE) - 30 and
> d.EML_ADDR_KEY = f.EML_ADDR_KEY)
> and a.BR_EMAIL_ADDRESS = b.EML_ADDR
> and b.EML_ADDR_KEY = d.EML_ADDR_KEY
>
> is this correct or is there a better way of writing it?
>
> thanks in advance!
> val
No version
No explain plan
No help possible.
I can't say that it is the issue but anytime you are using OR you need to very strategically place parentheses to make sure that the OR condition executes properly. You may be missing a few.
-- Daniel Morgan Puget Sound Oracle Users GroupReceived on Thu Oct 05 2006 - 11:14:54 CDT