Re: EXISTS and OR statement

> 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?

Personally, I use joins instead of EXISTS whenever possible. Have you considered rewriting this as a series of joins:

SELECT count(distinct b.EML_ADDR)
FROM table 1a, table 2 b, table 3 d, table 4 e, src_cust_eml_t f WHERE a.BR_EMAIL_ADDRESS = b.EML_ADDR

   and b.EML_ADDR_KEY = d.EML_ADDR_KEY
   and e.cust_key =d.cust_key
   and TRUNC(e.ins_dt) >= TRUNC(SYSDATE) - 30    and f.EML_OPT_OUT_IND = 'N' and TRUNC(f.ins_dt) >= TRUNC(SYSDATE) - 30    and ( (e.SLS_CHNL_TYP_CD='O' and brnd_key) = 1 OR (crd_typ_cd='BR')     OR d.EML_ADDR_KEY=f.eml_addr_key
    OR (d.EML_ADDR_KEY=f.eml_addr_key and d.EML_ADDR_KEY=f.EML_ADDR_KEY))

Something like that....

I'm not sure I followed the logic of your query to a tee. That is because some of it does not make any sense to me. In your query, you have the following:

 > 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)

The part that consfuese me iw that you have EML_OPT_OUT_IND='N' and TRUNC on the dates in both. Yet one has d.EML_ADDR_KEY = f.EML_ADDR_KEY and the other uses IN. Aren't those the same things?

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown