Problems using NOT IN with sum function in MS Query

[jack10063]

Hi All,

I am trying to combine sum function and NOT IN logical operator in MS Query SQL but it's not working and I am getting an error "Invalid field definition '000' in definition of index or relationship".

Below is my code:

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>

SELECT A.`BOND ISIN CODE`, A.`BOND TYPE`, A.`MATURITY DATE`, SUM(A.TOTAL) AS [TOTAL], B.PV, B.`BOND ISIN CODE`, B.TOTAL<o:p></o:p>
<o:p></o:p>
FROM BondIsin.txt A, BondIsinAcu.txt B<o:p></o:p>
<o:p></o:p>
WHERE A.`BOND ISIN CODE` = B.`BOND ISIN CODE` AND A.`BOND ISIN CODE` NOT IN ('XSDE0002449139')<o:p></o:p>
<o:p></o:p>
GROUP BY A.`BOND ISIN CODE`, A.`BOND TYPE`, A.`MATURITY DATE`, B.PV, B.`BOND ISIN CODE`, B.TOTAL<o:p></o:p>

The problem is the aggregate function Sum. Below is a code without summing and GROUP BY statement that doesn't give an error:

SELECT A.`BOND ISIN CODE`, A.`BOND TYPE`, A.`MATURITY DATE`, A.TOTAL, B.PV, B.`BOND ISIN CODE`, B.TOTAL<o:p></o:p>
<o:p></o:p>
FROM BondIsin.txt A, BondIsinAcu.txt B<o:p></o:p>
<o:p></o:p>
WHERE A.`BOND ISIN CODE` = B.`BOND ISIN CODE` AND A.`BOND ISIN CODE` NOT IN ('XSDE0002449139')<o:p></o:p>

So the question is: Which statement can I combine with aggregate functions to filter the data..? I tried to use CASE statement but couldn't get it to work either.. Thanks for any help!

-Jack

 

[xenou]

Hmmm,
You Have A.Total in your query:

SELECT 
	A.`BOND ISIN CODE`, 
	[B][COLOR="Red"]A[/COLOR][/B].TOTAL, 
	D.TOTAL 

FROM ...

Shouldn't it be C.Total:

SELECT 
	A.`BOND ISIN CODE`, 
	[B][COLOR="Red"]C[/COLOR][/B].TOTAL, 
	D.TOTAL 

FROM ...

Complete Query now (I think this is more readable):

SELECT 
	A.`BOND ISIN CODE`, 
	A.TOTAL, 
	D.TOTAL 

FROM 
	BondIsin.txt A, 
		(SELECT  
			B.`BOND ISIN CODE`, 
			SUM(B.TOTAL) AS [TOTAL] 
			FROM  BondIsin.txt B 
			GROUP BY  
			B.`BOND ISIN CODE`) C, 
	BondIsinAcu.txt D
 
WHERE 
	A.`BOND ISIN CODE`= C.`BOND ISIN CODE` 
	AND 
	A.`BOND ISIN CODE`=D.`BOND ISIN CODE` 
	AND 
	A.`BOND ISIN CODE` NOT IN ('BE6000075430')

 

[jack10063]

xenou,

Thanks for your answer, I really appreciate it. It is working now!!

I have been on vacation for a while and now it's time start thinking about Excel again..