Problem excuting SQL via Pass through

[Pyromantic]

I'm trying to run a pass-through query in Access on a Oracle9 DB but I keep getting an ORA-00936 (ie, I'm missing some syntax etc) but nothing seems to be wrong with my code. Am I missing something :

SELECT COMPANY, BRANCH, CONTRACT_ID, WEEKENDING_DATE, TRANSACTION_CODE, Sum(TRANSACTION_UNITS) AS SumOfTRANSACTION_UNITS, Sum(BILL_VALUE) AS SumOfBILL_VALUE, Sum(PAY_VALUE) AS SumOfPAY_VALUE, Sum([BILL_VALUE])-Sum([PAY_VALUE]) AS Margin, IIf(Sum([BILL_VALUE])=0 And Sum([PAY_VALUE])<>0,-1,(Sum([BILL_VALUE])-Sum([PAY_VALUE]))/Sum([BILL_VALUE])) AS Margin2, Abs(Sum([BILL_VALUE]))+Abs(Sum([PAY_VALUE])) AS Check, CREATED

FROM EDWARDSG.VIEW_CONTRACT_TRANSACTIONS_DG

GROUP BY COMPANY, BRANCH, CONTRACT_ID, WEEKENDING_DATE, TRANSACTION_CODE, CREATED

HAVING (Abs(Sum([BILL_VALUE]))+Abs(Sum([PAY_VALUE])))<>0
AND CREATED BETWEEN TO_DATE('05/09/2007','DD/MM/YYYY') AND TO_DATE('03/10/2007','DD/MM/YYYY')

Thanks for reading. Any help or pointers would be greatly appreciated.

 

[giacomo]

couple things I found...
* iif that is not an Oracle function.
* the not equal to operator in Oracle is != not <>
* Not sure if you can say BETWEEN TO_DATE('05/09/2007','DD/MM/YYYY') AND TO_DATE('03/10/2007','DD/MM/YYYY') I've only ever put dates in chronological order with a between. ie between march and may not between may and march... not in the same year anyway

hth,
Giacomo