Need help with SQL syntax - Navision Financials

beckwiga · 2006-11-01

I am working for a client who uses the Navision system. I am connected to the system via the Navision ODBC driver and am using a simple ODBC tool (ODBCTest) to run queries.

I need to query the data to return transactions that have a Date within a certain range. This is not hard normally, but I am getting errors when performing the simplest of SQL queries on this system. For example, so, I set up a SELECT Date FROM blank WHERE Date ... and have tried to use WHERE Date >= and have also tried WHERE Date IS GREATER THAN and have also tried WHERE Date BETWEEN and put in 2 dates, but none of the operators seem to be acceptable syntax to Navision. I also can't seem to use other functions like Left, Right, Mid, LTrim, Date_Format, etc...

Can anyone help me with SQL syntax that is accepted by this system? What functions are supported if any and what operators are acceptable? Does anyone have any documentation on using SQL with this system?

Thanks in advance... any suggestions appreciated. Email to beckwiga@hotmail.com

greg

nunomaia · 2006-11-01

FROM NOBDC Manual

DATE Enter a date in this format: {d ‘yyyy-mm-dd’} where y=year (1752-
9999), m=month (01-12) and d=day (01-31).
Note that if you have closing date support, you must use the
timestamp format described on page 23.

SELECT * FROM Currency
WHERE {pred SetFlowFilter, 'Currency', 'Customer_Filter',
'10000..40000' } OR
Last_Date_Modified > 01.01.04

Need help with SQL syntax - Navision Financials

Comments

Categories