Exclusive OR in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Exclusive OR in query

Author

Topic

plawrenz
Starting Member

15 Posts

Posted - 2007-12-12 : 15:10:55

I am trying to have a where statement that uses an or but I want it to be exclusive of each other. some of the transactions show up twice since they satisfy both statements. Here is the sql

where ((a.process_date = '12/11/2007' and d.process_date < '12/11/2007') or
(a.process_date = '12/11/2007')) ...

any suggestions?

PS I am querying a DB2 database

plawrenz
Starting Member

15 Posts

Posted - 2007-12-12 : 15:52:34

Here is the whole query please tell me how to make the or statement exclusive so I don't get duplicate rows of data that match both criteria

select a.portfolio_id,
a.transaction_code,
a.trade_date,
b.num_of_contracts,
a.exec_trade_number,
b.purchs_trde_number,
cast(case
when (a.RATE_CONV_CODE = '1' and a.trans_currency = 'USD') then b.clos_cntract_price
when (a.RATE_CONV_CODE = '0' and a.trans_currency = 'USD') then b.clos_cntract_price
when (a.RATE_CONV_CODE = '0' and a.trans_currency <> 'USD' and a.TRADE_EXCHANGE_RT <>0) then (b.clos_cntract_price/a.TRADE_EXCHANGE_RT)
when (a.RATE_CONV_CODE = '1' and a.trans_currency <> 'USD'and a.TRADE_EXCHANGE_RT <>0) then (b.clos_cntract_price*a.TRADE_EXCHANGE_RT)
else null end as numeric(18,9)) as clos_cntract_price,
a.commission_amount,
b.commission_amount,
c.commission_amount,
d.commission_amount,
case when (b.num_of_contracts - c.num_of_contracts = 0) then ((b.GNLS_AMT) - (b.commission_amount) - (c.commission_amount))
when (b.num_of_contracts - c.num_of_contracts - d.num_of_contracts = 0) then ((b.GNLS_AMT) - (b.commission_amount) - (c.commission_amount))
when (b.num_of_contracts - c.num_of_contracts - d.num_of_contracts <> 0) then ((b.GNLS_AMT) - (b.commission_amount)) else null end as glamount
from pfanml.tnn a, nml.ib4 b, pfanml.tnn c, pfanml.ib4 d

where ((a.process_date = '12/11/2007' and d.process_date < '12/11/2007') OR
(a.process_date = '12/11/2007'))

and
a.transaction_code = '286' and
a.portfolio_id = b.portfolio_id and
a.portfolio_id = c.portfolio_id and
a.portfolio_id = d.portfolio_id and
a.exec_trade_number = b.exec_trade_number and
b.purchs_trde_number = c.exec_trade_number and
c.exec_trade_number = d.purchs_trde_number and
a.portfolio_id in ('10020199','10020399','10020499','10020599','10020699','10030199','10030299');

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 16:31:01

a.process_date cannot be BOTH "equal to" and "less than" simultaneously.

E 12°55'05.25"
N 56°04'39.16"

plawrenz
Starting Member

15 Posts

Posted - 2007-12-12 : 18:42:32

a.process_date cannot be BOTH "equal to" and "less than" simultaneously.
it is not that above statement is it:

where ((A.process_date = '12/11/2007' and D.process_date < '12/11/2007') OR
(A.process_date = '12/11/2007'))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 18:48:56

Please provide a data example of the issue. The data example should include your problem of it showing up twice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-13 : 01:11:45

quote:
Originally posted by plawrenz

I am trying to have a where statement that uses an or but I want it to be exclusive of each other. some of the transactions show up twice since they satisfy both statements. Here is the sql

where ((a.process_date = '12/11/2007' and d.process_date < '12/11/2007') or
(a.process_date = '12/11/2007')) ...

any suggestions?

PS I am querying a DB2 database

Post your question at http://www.dbforums.com/forumdisplay.php?s=920bdb7367a2188b9387d44259197f09&f=8

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources