LEFT JOIN error

(0) Share

Report

Posted on by Community Member Microsoft Employee

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

All responses (15)

Answers (0)

Suggested answer

Alex A 2,348 on at

Like (0)

Report

RE: LEFT JOIN error

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

If needed (but please use with caution) you can put a FROM clause in your UPDATE statement to specify the table used to provide the criteria for the update operation. You can also write a Subquery and use that in your UPDATE statement to provide the criteria for the update operation:

See: UPDATE (Transact-SQL)

Setting Column Values
• Subqueries
msdn.microsoft.com/.../ms177523.aspx

Updating Data Based on Data From Other Tables

• FROM
msdn.microsoft.com/.../ms177523.aspx
Community Member Microsoft Employee on at

Like (0)

Report

RE: LEFT JOIN error

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
Alex A 2,348 on at

Like (0)

Report

RE: LEFT JOIN error

Ok good luck. Be sure to mark any of the answers that were helpful. Take care.
Community Member Microsoft Employee on at

Like (0)

Report

RE: LEFT JOIN error

I only want to group by entry number and the data must be grouped
Community Member Microsoft Employee on at

Like (0)

Report

RE: LEFT JOIN error

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
Suggested answer

Alex A 2,348 on at

Like (0)

Report
RE: LEFT JOIN error

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: LEFT JOIN error

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: LEFT JOIN error

Hi,

What is your exact reporting requirement, accordingly we can suggest the related query.
Suggested answer

Alex A 2,348 on at

Like (0)

Report

RE: LEFT JOIN error

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

Alex A 2,348 on at

Like (0)

Report

RE: LEFT JOIN error

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.