On Wed, 17 Oct 2012 14:02:45 +0200, Nols Smit <nols@...>
wrote:

>>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 much clearer than your original mail, and it most certainly is not
the same condition as you originally specified (granted: natural languages
are notoriously ambiguous in the meaning of 'or' and 'and').

> Is it possible not to use the UNION?

Sure it is:
p.Date_Closing is not null and p.Date_Closing >= :DateA and p.Date_Closing
<= :DateB
can be simplified to
p.Date_Closing BETWEEN DateA AND DateB
(the comparisons itself imply that Date_Closing is not null).

Then combine the condition of both sides of the to:

((p.Date_Closing is null OR p.Date_Closing BETWEEN DateA AND DateB) or
(p.TOTAL_REVENUE is null .... etc

Mark