You’re offline. This is a read only version of the page.
Hi I am trying to join three tables to one main table by using a left join
use [SpierLive] go select /*Item Ledger Entry Table*/ a.[Entry No_], max(a.[Source No_]), max(a.[Global Dimension 1 Code]) as [Brand Class], max(a.[Country_Region Code]) as [Country Code], sum(cast(a.[Quantity] as numeric)) [Quantity], sum(cast(a.[Remaining Quantity] as numeric)) [Remaining Quantity], max(a.[Variant Code]) as [Variant Code], /*Value Entry Table*/ --(b.[Posting Date]), max(b.[Inventory Posting Group]), max(b.[Global Dimension 1 Code] as [Brand Class Code]), sum(cast(b.[Valued Quantity] as numeric)) [Valued Quantity], sum(cast(b.[Sales Amount (Actual)] as numeric)) [Sales Amount (Actual)], sum(cast(b.[Cost Amount (Actual)] as numeric)) [Cost Amount (Actual)], sum(cast(b.[Expected Cost] as numeric)) [Expected Cost], sum(cast(b.[Sales Amount (Expected)] as numeric))[Sales Amount (Expected)], sum(cast(b.[Cost Amount (Expected)] as numeric)) [Cost Amount (Expected)], /*Item Table*/ max(c.[Inventory Posting Group]) as [Inventory Posting Group], max(c.[Item Category Code]) as [Item Category Code], max(c.[Base Unit of Measure]) as [Base Unit of Measure], sum(cast(c.[Unit Cost] as numeric)) [Unit Cost], sum(cast(c.[Standard Cost] as numeric)) [Standard Cost], sum(cast(c.[Last Direct Cost] as numeric)) [Last Direct Cost], /*WineMS Inv_Transaction tbl Table*/ max(d.[Company]) as [Company], sum(cast(d.[Quantity] as numeric)) [Quantity], sum(cast([Cost Amount] as numeric)) [WineMS Cost Amount], max(d.[Brand Group]) as [Brand Group], max(d.[Owner]) as [Owner] from [dbo].[Spier Live$Item Ledger Entry] a, [dbo].[Spier Live$Value Entry] b, [dbo].[Spier Live$Item] c, [dbo].[WineMS Inv_Transaction Tbl] d left join [dbo].[Spier Live$Item Ledger Entry] on a.[Item No_] = c.[No_] left join [dbo].[Spier Live$Value Entry] on b.[Item No_] =c.[No_] left join [dbo].[WineMS Inv_Transaction Tbl] on d.[Item No] = c.[No_] group by a.[Entry No_]
I am getting this error when I execute the script
Msg 4104, Level 16, State 1, Line 32 The multi-part identifier "a.Item No_" could not be bound. Msg 4104, Level 16, State 1, Line 32 The multi-part identifier "c.No_" could not be bound. Msg 4104, Level 16, State 1, Line 33 The multi-part identifier "b.Item No_" could not be bound. Msg 4104, Level 16, State 1, Line 33 The multi-part identifier "c.No_" could not be bound. Msg 4104, Level 16, State 1, Line 34 The multi-part identifier "c.No_" could not be bound. Msg 209, Level 16, State 1, Line 24
if I use a where clause the query runs to long , even though I have a group by entry no, what am I doing wrong.
*This post is locked for comments
Hi Jason,
There might be a way for the Jet Data Manager to do a complete re-load of your Data Warehouse from the NAV tables. Try contacting Jet Reports to see how to do that:
www.jetreports.com/.../microsoft-dynamics-nav.php
See: UPDATE (Transact-SQL)
Setting Column Values
• Subqueries
msdn.microsoft.com/.../ms177523.aspx
Updating Data Based on Data From Other Tables
Alex hope you can help , I am using Jet Data Manager / Timextender , don't know if you familiar with it but my issue I have is that the sales cost (actual) Column in Navision in the value entry table gets transformed into my data warehouse which is hosted by Jet Data Manager, my update script I sent on my other post made all the values in the sales cost (actual) column 0 , how would I get that column values back , should I use an update script to update the posted sales transactions fact table from the value entry table in nav
I only want to group by entry number and the data must be grouped
Thanks Alex , I will use the code and still group by entry no. ,however I need the winems table as well as it contains my Bulkwine and additives data
Jason,
When you are running into problems like this it's a good idea to strip your query down (go back to basics) so you can get your JOIN straightened out first. The Join makes all the difference. The first question you have to ask yourself is what table should you start your Join with? And this depends on what you're trying to achieve.
Not knowing exactly what you're driving toward but by looking at your Select statement, I'm guessing that you are wanting to start first with the Item Ledger table. So I tested your code (after reducing to a more basic approach and removing all the aggregate functions), it then became obvious that trying to Join the Value Entry table ON [VE].[Item No_] = [ILE].[Item No_] is causing the long run time. My question to you is why do you think you need to join this table on this field?
When I corrected the Join between these two tables the query finishes in 23 seconds without issue.
Take a look below, take this code and start over, testing the query each time you Join another table to make sure the Join is correct.
SELECT /*Item Ledger Entry Table*/ [ILE].[Entry No_], [ILE].[Source No_], [ILE].[Global Dimension 1 Code] AS [Brand Class], [ILE].[Country_Region Code] AS [Country Code], [ILE].[Quantity] AS [Quantity], [ILE].[Remaining Quantity] AS [Remaining Quantity],[ILE].[Variant Code] AS [Variant Code], /*Value Entry Table*/ [VE].[Posting Date], [VE].[Inventory Posting Group], [VE].[Global Dimension 1 Code] AS [Brand Class Code], [VE].[Valued Quantity] AS [Valued Quantity], [VE].[Sales Amount (Actual)] AS [Sales Amount (Actual)], [VE].[Cost Amount (Actual)] AS [Cost Amount (Actual)], [VE].[Expected Cost] AS [Expected Cost], [VE].[Sales Amount (Expected)] AS [Sales Amount (Expected)], [VE].[Cost Amount (Expected)] AS [Cost Amount (Expected)], /*Item Table*/ [I].[Inventory Posting Group] AS [Inventory Posting Group], [I].[Item Category Code] AS [Item Category Code], [I].[Base Unit of Measure] AS [Base Unit of Measure], [I].[Unit Cost] AS [Unit Cost], [I].[Standard Cost] AS [Standard Cost], [I].[Last Direct Cost] AS [Last Direct Cost] /*WineMS Inv_Transaction tbl Table*/ -- [WITT].[Company] AS [Company], [WITT].[Quantity] AS numeric) AS [Quantity], [Cost Amount] AS [WineMS Cost Amount], --[WITT].[Brand Group] AS [Brand Group], [WITT].[Owner] AS [Owner] FROM [Spier Live$Item Ledger Entry] [ILE] LEFT JOIN [Spier Live$Value Entry] [VE] ON [VE].[Item Ledger Entry No_] = [ILE].[Entry No_] -- THIS IS HOW YOU SHOULD JOIN THESE TABLES ------------------CAUSES THE LONG WAIT-----------------ON [VE].[Item No_] = [ILE].[Item No_] LEFT JOIN [Spier Live$Item] [I] ON [I].[No_] = [ILE].[Item No_] --LEFT JOIN [WineMS Inv_Transaction Tbl] -- ON [WITT].[Item No] = [ILE].[Item No_]
Regards,
Alex A
Hi Alex , I would like the Item ledger Entry table to join to item on [Item Ledger Entry].[Item No_] = [No_]
and Value Entry Table to Item on [Value Entry].[Item No_] = [Item].[No_]
and [WineMS Inv_Transaction Tbl].[Item No] = [Item].[No_]
and then as per my script I group by [Entry No_]
If you can select a few columns from your NAV application and test to see what happens you can get a clearer understanding , i also understand what you are suggesting , i can also join the value entry and item ledger entry , however i will send you my full objective a bit later
Hi,
What is your exact reporting requirement, accordingly we can suggest the related query.
Just guessing at the goal here but maybe try starting with the Item table first, and then LEFT JOIN the other tables to that. This will eliminate any problems with the Value Entry table not having an Item number (it will just make blanks if that happens). The starting table makes a difference with Left Joins. You can start with Item and then Left Join Item Ledger (to Item) and then Left Join Value Entry (to Item Ledger). Or you might start with Item and then Left Join both tables back to Item.
Hi Jason,
I was looking at it from a GL perspective as I didn't really know you're main objective, and I primarily wanted to give you an example of the proper syntax. I use the Entry column because the Value Entry Item# column doesn't always have a value depending on which G_L Account number I'm filtering by.
The Value Entry and Item Ledger Entry tables are like brother/sister tables and they generally relate best by the Entry column, but it does depend on your application. You can always use different columns for the Join if you'd like depending on what you're trying to achieve; just test your results.
Perhaps I can provide a more accurate response if you can explain the objective or the result you need to achieve. Would you be able to show us what the final report should look like, and what type of report you are trying to generate?
Additionally, if it helps any, I ran the query that I sent back to you and it finished in less than 30 seconds for me.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,151
Super User 2024 Season 2
#2
Martin Dráb
229,993
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (