Im writing this stored procedure and just need to add one more column to the mix. But i cant recall exactly what to do with joins. I have examples, but they all look alittle different. Anyways, i realized that the column i want to add (its highlighted) Needs to have a join. Here is my code. Can anyone explain to me, how i join this with the rest. IT is located in a table called Budget_Reporting_Group and the column is called Budget_Reporting_Group_Description. As you can see, I have another Table called Salesperson_Purchaser, Im pretty sure these two tables are relational by a Salesperson_Purchaser_ID. How would i do the join? If anything, can you display what it would look like (IE yourDatabase.dbo.TableName as Table_Name ON etc etc) I completely forgot the format.
USE [RC_STAT]
GO
/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_Copy] Script Date: 06/28/2007 09:49:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTERPROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY]
(@.Region int=Null)
AS
BEGIN
SELECT
[Item_Ledger_Posting_Datetime] Post_Date
,'SMP' Budget_Type
,'Invoice'as Document_Type
,[Item_Ledger_Document_No] Document_Number
,[Item_Description]+' '+'('+[Item_No]+')' Entry_Description
,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime,'SALESAMP')*-1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL *-1 ) Amount
,-1*[Item_Ledger_Invoiced_Qty] Quantity
,Customer_Name
,'' External_Doc_no
,[Item_Ledger_Sales_Responsible] SR_Code
,[Item_Ledger_Mars_Period_Code] ThePeriod
,[Item_Ledger_Mars_Year] TheYear
,Territory.Name AS Territory_Name
,Region.Region AS Region_Name
,Budget_Reporting_Group.Budget_Reporting_Group_Description
FROM [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]
INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser
ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory
ON Territory.Code = Salesperson_Purchaser.Territory_Code
INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting
ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code
LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region
ON Territory.Region_Key = Region.Region_Key
WHERE Region.Region_Key = @.Region
AND [Item_Ledger_Mars_Year] = 2007
UNIONALL
SELECT
[GL_Entry_Posting_DateTime]
,[Budget_Type_Code]
,[Document_Type]
,[GL_Entry_Document_No]
,[GL_Entry_Description]
,[GL_Entry_Amount]
,0 Quantity
,[User_ID]
,[GL_Entry_External_Document_No]
,[Sales_Responsible]
,[Mars_Period_Code]
,[Mars_Year]
,Territory.Name AS Territory_Name
,Region.Region AS Region_Name
,Budget_Reporting_Group.Budget_Reporting_Group_Description
FROM [NavisionReplication].[dbo].[Tbl_GL_entry] GL_entry
INNERJOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Budget_Accounts] Budget_Accounts
ON Budget_Accounts.[GL_Account_No] = GL_entry.[GL_Account]
INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser
ON GL_entry.[Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory
ON Territory.Code = Salesperson_Purchaser.Territory_Code
LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region
ON Territory.Region_Key = Region.Region_Key
INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting
ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code
WHERE GL_entry.[Mars_Year] = 2007
END
Code Snippet
INNER JOIN Budget_Reporting_Group
ON BUDGET_REPORTING_GROUP.Salesperson_Purchaser_ID = SALESPERSON_PURCHASER.Salesperson_Purchaser_ID
|||
I still keep getting this error:
sg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7
Invalid object name 'Tbl_Budget_Reporting_Group'.
Msg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7
Invalid object name 'Tbl_Budget_Reporting_Group'.
No comments:
Post a Comment