On Wed, 17 Oct 2012 14:02:45 +0200, Nols Smit <[email protected]> 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
