create sql query based flow filter

catiamatos1991catiamatos1991 Posts: 92Member
edited 2018-09-18 in SQL General
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.

efc37ybekvi8.png








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"




Answers

  • Slawek_GuzekSlawek_Guzek Posts: 1,522Member
    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;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • catiamatos1991catiamatos1991 Posts: 92Member
    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...
  • krikikriki Posts: 8,519Member, Moderator
    [Topic moved from 'NAV/Navision Classic Client' forum to 'SQL General' forum]

    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2018: 22 & 23 November 2018, Antwerp (Belgium)
  • krikikriki Posts: 8,519Member, Moderator
    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
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2018: 22 & 23 November 2018, Antwerp (Belgium)
  • catiamatos1991catiamatos1991 Posts: 92Member
    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'

    nqcbyri0bx3n.png

    With green should be the correct value...

    Here's the nav table info

    ogota693bggh.png


Sign In or Register to comment.