SQL Help Needed

[carl]

I am trying to find a querry that will exclude a line of data if the
UnderlyingSymbol is in the list and the TradeType is Pip.

I tried this but to no avail

SELECT BTA_Trades_April.*
FROM BTA_Trades_April
WHERE UnderlyingSymbol NOT IN
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT','CFC','COP','CSCO','DELL','DIA','DNDN','EBAY','EEM','EMC','F','FCX','FLEX','GE','GM','GS','HAL','HD','INTC','IWM','JAVA','JPM','LEH','MER','MNX','MO','MOT','MSFT','NEM','NYX','OIH','PFE','QCOM','QQQQ','RIMM','RIO','SBUX','SMH','SNDK','SPY','T','TGT','TXN','VLO','VZ','WFMI','WM','WMT','XLE','XLF','XOM','YHOO')
and TradeType<>'Pip';

Thank you in advance for your help.

 

[John Spencer]

Well, you said you wanted to exclude the record is TradeType was equal to PIP,
but your where statement said if the TradeType is NOT equal to PIP. So
perhaps all you need to do is change <> to =.

WHERE UnderlyingSymbol NOT IN
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT',
'CFC','COP','CSCO','DELL','DIA','DNDN','EBAY','EEM','EMC','F','FCX',
'FLEX','GE','GM','GS','HAL','HD','INTC','IWM','JAVA','JPM','LEH','MER',
'MNX','MO','MOT','MSFT','NEM','NYX','OIH','PFE','QCOM','QQQQ','RIMM',
'RIO','SBUX','SMH','SNDK','SPY','T','TGT','TXN','VLO','VZ','WFMI','WM',
'WMT','XLE','XLF','XOM','YHOO')
and TradeType ='Pip'

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[KARL DEWEY]

and the TradeType is Pip.
And TradeType = "Pip";

 

[carl]

Thank you all. I am probably not explaining well.

I need the query to return the line of data only if the UnderlyingSymbol is
in the list AND the TradeType=Pip. For example, if the UnderlyingSymbol is
'A', and the TradeType is not 'Pip', I would like the query to return the
line of data.

Thank you in advance again.

 

[John W. Vinson]

I need the query to return the line of data only if the UnderlyingSymbol is
in the list AND the TradeType=Pip. For example, if the UnderlyingSymbol is
'A', and the TradeType is not 'Pip', I would like the query to return the
line of data.

I'm sorry, Carl, but that statement contradicts itself:

AND the TradeType=Pip

and the TradeType is not 'Pip'


Which do you want? You can have either one, but it can't be both =Pip and not
equal to Pip.

 

[KARL DEWEY]

....AND the TradeType=Pip.
....and the TradeType is not 'Pip',
You are contradicting yourself. Which is it - equal OR not equal?
With this set of data is the
Return column correct?
UnderlyingSymbol TradeType Return
A Pip Yes
AA Pipe No
AAPL Pip Yes
BIG Pip No
AMD Pip Yes
AMGN Pip Yes
AMZN Pip Yes
Or is backwards?

 

[John Spencer]

I think what you are saying is

-- IF in the list and TradeType = PIP return the record
Plus
-- If the trade type is NOT Pip (ignore the list) return the record

So perhaps what you want is the following

WHERE TradeType = 'PIP'
OR ( UnderlyingSymbol NOT IN
('A','AA','AAPL','AIG','AMD','AMGN','AMZN','BAC','BMY','BSC','C','CAT',
'CFC','COP','CSCO','DELL','DIA','DNDN','EBAY','EEM','EMC','F','FCX',
'FLEX','GE','GM','GS','HAL','HD','INTC','IWM','JAVA','JPM','LEH','MER',
'MNX','MO','MOT','MSFT','NEM','NYX','OIH','PFE','QCOM','QQQQ','RIMM',
'RIO','SBUX','SMH','SNDK','SPY','T','TGT','TXN','VLO','VZ','WFMI','WM',
'WMT','XLE','XLF','XOM','YHOO')
and TradeType <> 'Pip' )

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Sylvain Lafontaine]

Your sql code is OK; so the error might be somewhere else. You should post
an exemple of data - with the relevant table's schema - showing us something
that doesn't work as expected. You should also tell us how you are making
this query.