>I am not sure what problem you are having, but the conditions you
>describe are a contradiction.
>
>The following SQL give the correct results:
>
>select p.ID, p.BIZTYPE_DESCRIPTION, p.CostingType_Description, p.Description,
>p.CGS_Description, p.Date_Closing,
>p.TOTAL_REVENUE, p.EXCHANGE_RATE, p.CGSSHARE_PCT from V_BD_Biz_Prim p where
>(p.BIZTYPE_ID = :BizType_ID) and
>((p.Date_Closing is null) or (p.TOTAL_REVENUE is null or p.TOTAL_REVENUE = 0)
>or (p.EXCHANGE_RATE is null or
>p.EXCHANGE_RATE = 0) or (p.CGSSHARE_PCT is null or p.CGSSHARE_PCT = 0))
>
>union
>
>select p.ID, p.BIZTYPE_DESCRIPTION, p.CostingType_Description, p.Description,
>p.CGS_Description, p.Date_Closing,
>p.TOTAL_REVENUE, p.EXCHANGE_RATE, p.CGSSHARE_PCT from V_BD_Biz_Prim p where
>(p.BIZTYPE_ID = :BizType_ID) and
>((p.Date_Closing is not null and p.Date_Closing >= :DateA and p.Date_Closing
><= :DateB) or (p.TOTAL_REVENUE is null or
>p.TOTAL_REVENUE = 0) or (p.EXCHANGE_RATE is null or p.EXCHANGE_RATE = 0) or
>(p.CGSSHARE_PCT is null or p.CGSSHARE_PCT = 0))
This is a very different query, Nols, and can simply be rewritten
select p.ID, p.BIZTYPE_DESCRIPTION, p.CostingType_Description, p.Description,
p.CGS_Description, p.Date_Closing,
p.TOTAL_REVENUE, p.EXCHANGE_RATE, p.CGSSHARE_PCT from V_BD_Biz_Prim p
where p.BIZTYPE_ID = :BizType_ID
and (p.Date_Closing is null
or p.Date_Closing between :DateA and :DateB
or coalesce(p.TOTAL_REVENUE, 0) = 0
or coalesce(p.EXCHANGE_RATE, 0) = 0
or coalesce(p.CGSSHARE_PCT, 0) = 0)
So, where do you want OR and where do you want AND?
HTH,
Set
