#### Howdy, Stranger!

It looks like you're new here. Sign in or register to get started.

# create sql query based flow filter

Member Posts: 158
edited 2018-09-18
HI everyone,

I'm trying to create the sql query code based on a flow filter calc formula.

The formula is "Sum("Detailed Vendor Ledg. Entry".Amount WHERE (Vendor Ledger Entry No.=FIELD(Entry No.),Posting Date=FIELD(Date Filter),Excluded from calculation=CONST(No)))" and I "convert" this to

Select Sum([E18375\$Detailed Vendor Ledg_ Entry].[Amount]) as 'Valor pendente' FROM [E18375\$Detailed Vendor Ledg_ Entry] INNER JOIN [E18375\$Vendor Ledger Entry] ON [E18375\$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_] = [E18375\$Vendor Ledger Entry].[Entry No_] WHERE [E18375\$Detailed Vendor Ledg_ Entry].[Excluded from calculation] = 0 AND [E18375\$Detailed Vendor Ledg_ Entry].[Document No_]='1707NC006'
AND YEAR([E18375\$Detailed Vendor Ledg_ Entry].[Posting Date])>=2017

The problem is I get the value "Valor pendente" equals to "1838" instead of "10" like the image below.

I follow this example i found online to create my sql query..

Sum("Detailed Cust. Ledg. Entry"."Amount (LCY)" WHERE (Cust. Ledger Entry No.=FIELD(Entry No.),Entry Type=FILTER(Initial Entry),Posting Date=FIELD(Date Filter)))

Select Sum([Amount (LCY)] FROM [Detailed Cust. Ledg. Entry]
INNER JOIN
[Cust. Ledg. Entry]
ON
[Detailed Cust. Ledg. Entry].[Entry No.] = [Cust. Ledg. Entry].[Entry No.]
WHERE
[Detailed Cust. Ledg. Entry].[Entry Type] = "Initial Entry"

• Member Posts: 1,690
edited 2018-09-14
As a starting point the predicate
```..ON [E18375\$Detailed Vendor Ledg_ Entry].[Entry No_] = [E18375\$Vendor Ledger Entry].[Entry No_] ..
```
should rather go like this
```ON [E18375\$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_] = [E18375\$Vendor Ledger Entry].[Entry No_]...
```
Slawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
• Member Posts: 158
edited 2018-09-17
Even with this query I don't get the correct result
```select 'E18375' as Empresa, [E18375\$Detailed Vendor Ledg_ Entry].[Posting Date], [E18375\$Detailed Vendor Ledg_ Entry].[Document No_], [E18375\$Detailed Vendor Ledg_ Entry].[Vendor No_], [E18375\$Detailed Vendor Ledg_ Entry].Amount, (Select Sum([Amount (LCY)]) FROM [E18375\$Detailed Vendor Ledg_ Entry] INNER JOIN [E18375\$Vendor Ledger Entry] ON [E18375\$Detailed Vendor Ledg_ Entry].[Entry No_] = [E18375\$Vendor Ledger Entry].[Entry No_] WHERE [E18375\$Detailed Vendor Ledg_ Entry].[Excluded from calculation]=0 AND [E18375\$Detailed Vendor Ledg_ Entry].[Amount]>0) as 'Valor pendente' from [E18375\$Vendor Ledger Entry] inner join [E18375\$Detailed Vendor Ledg_ Entry] on [E18375\$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_]=[E18375\$Vendor Ledger Entry] .[Entry No_] where [E18375\$Vendor Ledger Entry].[Open]=1 and [E18375\$Detailed Vendor Ledg_ Entry].[Document No_]='1707NC006'
group by [E18375\$Detailed Vendor Ledg_ Entry].[Posting Date], [E18375\$Detailed Vendor Ledg_ Entry].[Document No_], [E18375\$Detailed Vendor Ledg_ Entry].[Vendor No_], [E18375\$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_], [E18375\$Detailed Vendor Ledg_ Entry].[Amount]
having sum([E18375\$Detailed Vendor Ledg_ Entry].[Amount])>0
```

It returns a amount of "3510098.20000000000000000000" for "Valor Pendente" instead of "10". Maybe the group by clause is wrong? or the having.. i don't get it

maybe the problem is the flow filter conversion...
• Member, Moderator Posts: 9,100
[Topic moved from 'NAV/Navision Classic Client' forum to 'SQL General' forum]

Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

• Member, Moderator Posts: 9,100
PS: AND [E18375\$Detailed Vendor Ledg_ Entry].[Document No_]='1707NC006'
Shouldn't that filter in the document no. in the vendor ledger entry? Because in the detailed it can have multiple values for 1 vendor ledger entry.

And I would turn around the query:

SELECT dvle.SUM(Amount)
FROM "Vendor Ledger Entry" vle
INNER JOIN "Detailed Vendor Legd_ Entry" dvle
ON (dvle."Vendor Ledger Entry No_" = vle."Entry No_")
AND (dvle."Excluded From Calculation" = 0)
AND (dvle.[Posting Date] >= '2017-01-01')
AND (dvle.[Posting Date] <= '2017-12-31');
WHERE vle.[Document No_]='1707NC006'

# AVOID AT ALL COST : YEAR("Some Date") = 2017. This forces SQL to scan the whole table or index!.

PS2: the INNER JOIN with the "Excluded from calculation" could have as a result that vendor ledger entries are skipped if No detailed ledger entries are selected for a vendor ledger entry
Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

• Member Posts: 158
Hi @kriki, I try your query and it returns 1838 (same value as Amount) and not the 10 (as the image below)

SELECT SUM([E18375\$Detailed Vendor Ledg_ Entry].[Amount]) FROM [E18375\$Vendor Ledger Entry] WITH(READUNCOMMITTED) INNER JOIN [E18375\$Detailed Vendor Ledg_ Entry] WITH(READUNCOMMITTED) ON ([E18375\$Detailed Vendor Ledg_ Entry].[Vendor Ledger Entry No_] = [E18375\$Vendor Ledger Entry].[Entry No_]) AND ([E18375\$Detailed Vendor Ledg_ Entry].[Excluded From Calculation] = 0) AND ([E18375\$Detailed Vendor Ledg_ Entry].[Posting Date] >= '2017-01-01') AND ([E18375\$Detailed Vendor Ledg_ Entry].[Posting Date] <= '2017-12-31') WHERE [E18375\$Vendor Ledger Entry].[Document No_]='1707NC006'

With green should be the correct value...

Here's the nav table info