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" o
referrelative="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><v
ath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v
ath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>
The problem is the aggregate function Sum. Below is a code without summing and GROUP BY statement that doesn't give an error:
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
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" o





Code:
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:
Code:
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