Same amount of rows in Sql vs Navision

(0) Share

Report

Posted on by Community Member

good day

I am wrote a script where I joined my new table Transfer Excise to Value Entry table the Transfer excise table contains data from the Location , excise rate , item tables. I then joined it to the value entry like this: I need the same amount of rows when I add the filters in NAV. however I do not get the same amount of rows , if I do the filters in SQL

USE SPIERLIVE;
GO

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 LEFT JOIN [Spier Live$Value Entry] b
ON a.[No_] = b.[Item No_] 
WHERE b.[Item No_] = 'F00335'
AND b.[Posting Date] > '2014-01-01'
AND b.[Location Code] = a.[Location Code]
AND b.[Gen_ Bus_ Posting Group] IN ('LOCA','EXSA')
--AND b.[Posting Date] >= a.[Starting Date]

*This post is locked for comments

All responses (11)

Answers (0)

Community Member on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision

Alexander , I have created a table comparison between the value entry table and transfer excise Tbl and you will notice that there is some NULL values in the Transfer Excise Tbl, due to it not having a matching corresponding Item no in the value entry table. I just want the corresponding No in the Transfer Excise Table to match the item no's in Value entry table
Community Member on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision

Below Is the screenshots of how the Transfer Excise Tbl looks like , of how the Value Entry Table looks like and how the result in Navision looks like. When I join Transfer Excise with Value Entry I must get the same result that is in Navision . see screenshots

Transfer Excise Tbl

Value Entry Table

Navision - Value Entry Card
Community Member on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision

I think that my sql script is duplicating , the important thing is , is the posting date when the item was posted / when the first transaction occurred , the starting date is static , we manually enter excise rate dates when enter a new rate for our financial year , the excise rate starting date occurs once a year. so we work on when the transaction was posted so therefore posting date is important. surely when I test the data in NAV the amount of data nav displays does not correspond to what the sql script displays
Suggested answer

Alexander Ermakov 28,094 on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision

This is what I'm trying to figure out - to me it looks like your SQL script filtering is joining one item transaction to four lines of setup in the Excise Rate table. I'll have a closer look soon.
Community Member on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision

Hi Alexander I don't understand what you saying, my starting date is from my Excise Rate table in Navision that is the dates when we entered our Excise rates for our different locations and the posting dates is when the item was posted based based on the starting date and unit rate of the item

so what are you trying to tell me. The value entry does not have a starting date. if Navision gives me one row how can sql give me 4 rows. that means the script I am doing is incorrect
Suggested answer

Alexander Ermakov 28,094 on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision

You have different values in Starting date, which all satisfies the condition of your filtering. I suppose if you would apply the same filtering to the table with Starting date field, you will also get 4 rows...
Community Member on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision

Vishal I cannot just use the value entry table , I need to join it to my new table to calculate the Excise Rate. I have now tested only one item no with the sql script for a particular posting date and sql displays 4 rows , I have applied the same filters in NAV and Navision displays 1 entry , what is wrong with my SQL script or what am I doing wrong
Suggested answer

Nareshwar Raju Vane... 5,596 on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision

Hi,

Also, if you want to use JOINs, you can use the same with a Query object in NAV.

Hope it helps.
Suggested answer

Community Member on at

Like (1)

Report

RE: Same amount of rows in Sql vs Navision

Better you use only Value Entry table in SQL as well, as in NAV you are using only single table.

Also fetch the data from Value Entry Table with Left Join of Transfer Excise Table.
Community Member on at

Like (0)

Report

RE: Same amount of rows in Sql vs Navision