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:47:41

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 15:48:27

Locking thread due to duplicate. Please post your replies in your other thread.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources