June 29, 2016 at 5:56 am
Hi
I am using Microsoft Dynamics Navision and SQL as the source
I have two tables the main table being Value Entry that holds all the transactions being done daily and my second table being the Transfer Excise Tbl which contains the Unit Rate and litre conversion values.
The Value Entry Table contains Item No's as well as the Transfer Excise Table. I need to get the exact amount item transaction if I join the two tables where there is a matching item vs Microsoft Dynamics Navision. I have tested my query against Navision and SQL give me much more transactions than Navision. for example if I test it on one item , sql gives me 941 rows and Navision for that same item gives me 460 rows. Either sql is returning duplicates or my script is wrong. I need all item transactions in the value entry table that does not exist in the Transfer Excise table
SELECT DISTINCT a.[Starting Date],
b.[Posting Date],
b.[Item No_],
b.[Invoiced Quantity],
a.[Litre Conversion Factor],
a.[Unit Rate] ,
a.[Location Code],
a.[Excise Location],
a.[Excise Type Code],
a.[Unit Of Measure Code]
FROM [Transfer Excise Tbl] a , [Spier Live$Value Entry] b
WHERE b.[Posting Date] > '2013-02-26 '
And a.[No_] = b.[Item No_]
AND b.[Location Code] = a.[Location Code]
AND b.[Gen_ Bus_ Posting Group] IN ('LOCA','EXSA')
AND b.[Posting Date] >= a.[Starting Date]
AND b.[Invoiced Quantity] <>0
AND b.[Item No_] = 'F00335'
Order By b.[Posting Date]
June 29, 2016 at 6:50 am
We don't know your table structure or your data, making this rather challenging.
Perhaps you could use Profiler to find out what query, or queries, Navision is executing and compare those with yours?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 29, 2016 at 7:24 am
The Transfer Excise Tbl is not a Navision Table it is a table I have created because I inserted data from various tables within Navision. So now I am trying to join the table to a table in Navision
as per my first post
June 29, 2016 at 7:27 am
Would it be possible to post my table structure here for you to see. What will the profiler do , I don't want to do a trace
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply