April 26, 2010 at 7:32 am
I am trying to join three tables.
The third one is to a linked server. I havn't done this before but it should be easy (I think).
I keep getting an error in this part of the join:
On dbo.tbl_TimeCard_Upload_PM.Job_Co = PR_Emp_Mst.Co_No
And dbo.tbl_TimeCard_Upload_PM.Employee_MEI_ID = PR_Emp_Mst.Emp_No
If I comment out all of the last join including the above statement the query runs fine. So its just joining this last table.
Any ideas???
SELECT
dbo.tbl_T_Upload_PM.WeekEnding As WeekEnding
--, PR_Emp_Mst.Emp_Name As Emp_Name
, dbo.tbl_T_Upload_PM.Employee_MEI_ID As Employee_MEI_ID
, dbo.tbl_T_Upload_PM.Job_Co As Job_Co
, dbo.tbl_T_Upload_PM.Job_Div As Job_Div
, dbo.tbl_T_Upload_PM.Job_No As Job_No
, dbo.tbl_T_Upload_PM.Main_Job_No As Main_Job_No
, dbo.tbl_T_Upload_PM.Sub_Job_No As Sub_Job_No
, dbo.tbl_T_Upload_PM.Emp_Class_ID_WD As Emp_Class_ID_WD
, dbo.tbl_T_Upload_PM.JCD As JCD
, dbo.tbl_T_Upload_PM.JCD_Seg1 As JCD_Seg1
, dbo.tbl_T_Upload_PM.JCD_Seg2 As JCD_Seg2
, dbo.tbl_T_Upload_PM.JCD_Seg3 As JCD_Seg3
, dbo.tbl_T_Upload_PM.Pay_Type_ID As Pay_Type_ID
, dbo.tbl_T_Upload_PM.Weekday_1 As Weekday_1
, dbo.tbl_T_Upload_PM.Weekday_2 As Weekday_2
, dbo.tbl_T_Upload_PM.Weekday_3 As Weekday_3
, dbo.tbl_T_Upload_PM.Weekday_4 As Weekday_4
, dbo.tbl_T_Upload_PM.Weekday_5 As Weekday_5
, dbo.tbl_T_Upload_PM.Weekday_6 As Weekday_6
, dbo.tbl_T_Upload_PM.Weekday_7 As Weekday_7
, dbo.tbl_T_Upload_PM.PM_Reviewed As PM_Reviewed
FROM
dbo.tbl_WeekEnding_PR
Left Join dbo.tbl_T_Upload_PM
ON dbo.tbl_T_Upload_PM.WeekEnding = dbo.tbl_WeekEnding_PR.dt_WeekEnding_Current
Left Join dbo.Review_tbl_Job_Access
ON dbo.Review_tbl_Job_Access.Co_No = dbo.tbl_T_Upload_PM.Job_Co
AND dbo.Review_tbl_Job_Access.Div_No = dbo.tbl_T_Upload_PM.Job_Div
AND dbo.Review_tbl_Job_Access.Job_No = dbo.tbl_T_Upload_PM.Job_No
Left Join
(
Select
PR_Emp_Mst.Co_No As Co_No
, PR_Emp_Mst.Emp_No As Emp_No
, PR_Emp_Mst.Emp_Name As Emp_Name
From
OpenQuery
(
Alpha,
'
SELECT
PR_Emp_Mst.MCONO as Co_No
, PR_Emp_Mst.MNM25 As Emp_Name
, PR_Emp_Mst.MEENO As Emp_No
FROM
PRTMST As PR_Emp_Mst
Where
(
(PR_Emp_Mst.MCONO = 1)
And (PR_Emp_Mst.DivisionNumber = 0)
)
'
)
As PR_Emp_Mst
)
On dbo.tbl_T_Upload_PM.Job_Co = PR_Emp_Mst.Co_No
And dbo.tbl_T_Upload_PM.Employee_MEI_ID = PR_Emp_Mst.Emp_No
WHERE
(
(dbo.Review_tbl_Job_Access.MEI_User_ID = RIGHT(SUSER_SNAME(), 6))
AND (dbo.tbl_T_Upload_PM.PR_Upload = 0)
AND (dbo.tbl_WeekEnding_PR.b_WeekEnding_Locked = 0)
)
I appreciate any help you can give me. I can't find anything wrong so it is probably something simple.
Thank you,
April 26, 2010 at 7:51 am
You are missing an alias to the select statement in the left join. You did use an alias for the openquery function, but you also need to use an alias for the select statement that is using the openquery because it is a sub query that you run in the from clause.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 26, 2010 at 7:54 am
Thank you,
Worked first try. I was simple after all. I did move the alias but never thought I needed a second one.
I feel a little foolish.
Thank you again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply