Hi Group
I’m devoloping a BI system, that uses ODBC to access our nav 4.0 sp1 native database.
I’m having problems selecting Invoices with a specifik amount.
When I submit the following sql
Select *
From "Sales Invoice Header"
Where Amount <= 5454
I get a number of Invoices including the one with amount 5454.
But when I make the following query, I don’t get any rows returned:
Select *
From "Sales Invoice Header"
Where Amount = 5454
What am I doing wrong ?
Basically that looks all OK; I could imagine that the actual amount of the row returned with the first query is not exactly 5454 but maybe 5453.99999 or something … any rounding issue?
No, I don’t think so
Select *
From "Sales Invoice Header"
Where Amount >= 5453
Does not return any rows at all !!! … should return 9 rows from the CRONUS database.
Could it be a BCD datatype issue ?
This sql only returns the invoice with Amount = 5454 … not any of the other 11 invoices with amounts between 0 and 5454.
Select *
From "Sales Invoice Header"
Where Amount >= 0.005453
And Amount <= 5454
It doen’t make any sense to me 
No, this makes absolutely no sense … but without seeing the real data it’s hard to tell what’s wrong … ???
In Standard NAV “Amount” in “Sales Invoice Header” (T112) is a FlowField, thus culculated on demand. So I guess there was some customization done, maybe a little bug in there?!
I’m using a standard NAV 4.0 SP1 installation and a ODBC connection.
The data is the CRONUS sample database and the queries are made from Excel.
It seemes like it’s a FlowField thing.
This gives me the correct Invoice
Select “Document No.”, Sum(Amount)
From "Sales Invoice Line"
Group By "Document No."
Having Sum(Amount) = 5454
But is this really a way that would perform good enough in a real life database ?
How can I implement a FlowField in the where clause of the sql (in Excel Query) ?
Yeah, this is a general problem with NAV … for all FlowFields you need to include some sub-qqueries or something … this sucks, indeed …
Once I created a small pretty basic tool to generate these sub-sql-queries: http://www.mibuso.com/dlinfo.asp?FileID=1101
Alternatively you could use the NAV C/FRONT “API” to use the C/AL command stack (thus CALCFIELDS and FlowFields) but I have no experiences how to use that with Excel …
As normally a real FlowField does not even exist within the SQL table you cannot use it in the WHERE clause.
Thank you for your help, Jörg … I’ll check out your tool.