Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / conflict result in vi...
Microsoft Dynamics NAV (Archived)

conflict result in view selection data from dynamic NAV2009

(0) ShareShare
ReportReport
Posted on by ahmed barbary 4,548

Hi guys i need to make view select in sql server 2005 to show the data from dynamic nav

database but result not true 

data i need to select from

Sales header table

Sales line table

all lines found in tables sales line is 88 rows

and header is 92

when i make view it give me wrong result

THIS GIVE ME 6251 records why it give me this error

my query i write is 

SELECT dbo.[Jeddah-Live$Sales Line].[Document Type], dbo.[Jeddah-Live$Sales Line].[Document No_], dbo.[Jeddah-Live$Sales Line].[Shipment Date],
dbo.[Jeddah-Live$Sales Line].Description, dbo.[Jeddah-Live$Sales Line].Quantity, dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity],
dbo.[Jeddah-Live$Sales Header].[Bill-to Customer No_]
FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN
dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].[Document Type] = dbo.[Jeddah-Live$Sales Line].[Document Type]

How to make right selection 

*This post is locked for comments

  • ahmed barbary Profile Picture
    ahmed barbary 4,548 on at
    RE: conflict result in view selection data from dynamic NAV2009

    Meaning i need to add balance(sum) of all debit amount from starting until today and also  add balance(sum) of all credit amount from starting until date today  

  • ahmed barbary Profile Picture
    ahmed barbary 4,548 on at
    RE: conflict result in view selection data from dynamic NAV2009

    Thank you for reply

    OK it work

    But remaining one point if possible

    I need to add to query based on date today (debit amount) and credit amount to every customer until today

    How i do that 

  • mmv Profile Picture
    mmv 11,465 on at
    RE: conflict result in view selection data from dynamic NAV2009

    Hi,

    Please try the below:-

    SELECT dbo.[Jeddah-Live$Sales Line].[Document Type], dbo.[Jeddah-Live$Sales Line].[Document No_], dbo.[Jeddah-Live$Sales Line].[Shipment Date],

    dbo.[Jeddah-Live$Sales Line].Description, dbo.[Jeddah-Live$Sales Line].Quantity, dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity],

    dbo.[Jeddah-Live$Sales Header].[Bill-to Customer No_]

    FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN

    dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].[Document Type] = dbo.[Jeddah-Live$Sales Line].[Document Type] AND dbo.[Jeddah-Live$Sales Header].[No_] = dbo.[Jeddah-Live$Sales Line].[Document No_]

    WHERE DATEDIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0

    Best Regards,

    MMV

  • ahmed barbary Profile Picture
    ahmed barbary 4,548 on at
    RE: conflict result in view selection data from dynamic NAV2009

    ok thank you for reply

    but remaining one thing 

    if i need to get data based on date today only

    How i do that from view

    i write view but it return nothing 

    this is my view

    SELECT ROW_NUMBER() OVER (ORDER BY dbo.[Jeddah-Live$Sales Header].No_) AS [م], dbo.[Jeddah-Live$Sales Line].[Document No_] AS 'رقم الطلب',
    dbo.[Jeddah-Live$Sales Header].[Bill-to Name] AS 'العميل', dbo.[Jeddah-Live$Sales Line].Area AS 'نوع الصبه', dbo.[Jeddah-Live$Sales Line].Description AS 'البيان',
    dbo.[Jeddah-Live$Sales Header].[Pump No_] AS 'المضخه', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].Quantity, 0, 1) AS int) AS 'المطلوب',
    CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Quantity Shipped], 0, 1) AS int) AS 'المصبوب', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity], 0,
    1) AS int) AS 'المتبقى '
    FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN
    dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].No_ = dbo.[Jeddah-Live$Sales Line].[Document No_] AND
    dbo.[Jeddah-Live$Sales Header].[Sell-to Customer No_] = dbo.[Jeddah-Live$Sales Line].[Sell-to Customer No_] AND
    dbo.[Jeddah-Live$Sales Line].[Shipment Date] = GETDATE()

    Although today have more transactions but not show why

    before last "AND" IT work without any problem 

    after AND 
    dbo.[Jeddah-Live$Sales Line].[Shipment Date] = GETDATE()

    show nothing without error in syntax

  • mmv Profile Picture
    mmv 11,465 on at
    RE: conflict result in view selection data from dynamic NAV2009

    Hi,

    Does the above solution work for you?  Could you verify the solution?

    Best Regards,

    MMV

  • mmv Profile Picture
    mmv 11,465 on at
    RE: conflict result in view selection data from dynamic NAV2009

    SELECT dbo.[Jeddah-Live$Sales Line].[Document Type], dbo.[Jeddah-Live$Sales Line].[Document No_], dbo.[Jeddah-Live$Sales Line].[Shipment Date],

    dbo.[Jeddah-Live$Sales Line].Description, dbo.[Jeddah-Live$Sales Line].Quantity, dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity],

    dbo.[Jeddah-Live$Sales Header].[Bill-to Customer No_]

    FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN

    dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].[Document Type] = dbo.[Jeddah-Live$Sales Line].[Document Type] AND dbo.[Jeddah-Live$Sales Header].[No_] = dbo.[Jeddah-Live$Sales Line].[Document No_]

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans