create sql query based flow filter

catiamatos1991

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"

Slawek_Guzek

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_]...

catiamatos1991

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...

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'SQL General' forum]

kriki

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
WITH(READUNCOMMITTED) // to avoid putting sharelocks on the table while reading
INNER JOIN "Detailed Vendor Legd_ Entry" dvle
WITH(READUNCOMMITTED) // to avoid putting sharelocks on the table while reading
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

catiamatos1991

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