TIMESTAMPADD() is giving me an operand error inside the where clause and I cant figure out why

55 Views Asked by At

I am using a version of SQL for Advantage Databases.

In the below code, I am attempting to retrieve the total amount from the past year for each customer. However, the TIMESTAMPADD() WHERE function will not work in its current form and gives me an error "[iAnywhere Solutions][Advantage SQL Engine]Invalid operand for operator: >"

SELECT 
    Maccount.SortName AS Customer,
    SUM(Journal.GLAmount) AS Billings
FROM Journal
    INNER JOIN MAccount
        ON Journal.MAccountPkey=MAccount.MAccountPkey
WHERE Journal.Type=150
AND   Journal.Date > TIMESTAMPADD(SQL_TSI_YEAR,-1,CURDATE())
GROUP BY 1
ORDER BY 1,2;

I have tried moving the timestampadd() to a HAVING clause, where it is giving me the same error. The function works fine by itself, so I know it isn't a syntax error within the function itself.

Journal.Date < CURDATE(), for example, works fine in the where clause, so I'm not exactly sure why it will not accept a ">" with this.

Any help is appreciated.

0

There are 0 best solutions below