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

I have the same question (0)

All responses (11)

Answers (0)

Nareshwar Raju Vane... 5,596 on at

Like (0)

Report

Hi,

Please upload the NAV screenshot again. It is not uploaded correctly.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Nareshwar Raju Vane... 5,596 on at

Like (0)

Report

Hi,

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

Hope it helps.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Alexander Ermakov 28,096 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Alexander Ermakov 28,096 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No