I'm attempting to create the correct set of joins on multiple tables across two different databases but i keep running into syntax errors ("Incorrect syntax near the keyword 'join') whenever i run the following query. What's causing this and how can i get around it?
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;
DECLARE @myNumber as VarCHAR(MAX)
SET @myNumber = CAST(@myNumber AS INT)
DECLARE @myBook INT;
SELECT @myBook = myBook from locationMaster WHERE myNumber = @myNumber AND (SearchType='S' OR myType ='CS' OR myType ='TS');
;WITH fifthTable AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY
serial
ORDER BY
CASE encode.Primaryencode
WHEN 'A' THEN 1
WHEN 'P' THEN 2
END,
isr.EffectiveDate DESC
) AS 'RowNumber',
encode.encode,
ISNULL(isr.myPrice, '0.00') as myPrice,
isr.workItemNumber,
PE.myEventID as EventID,
im.Description
FROM [DB1].[dbo].[firstTable] as firstTableAlias
Join alias1 im
on firstTableAlias.SIN = im.SIN
and firstTableAlias.MaxExpirationDate > GetDate()
and firstTableAlias.myBook = @myBook
JOIN secondTable isr
ON (firstTableAlias.workItemNumber = isr.workItemNumber
AND isr.myBook = @myBook
AND isr.ExpirationDate > GetDate())
JOIN thirdTable encode
ON encode.workItemNumber = isr.workItemNumber
AND ( encode.Primaryencode='A' OR (encode.Primaryencode='P' AND LEN(encode.encode) = 12) )
JOIN fourthTable AS ia
ON ia.PriceBookItem = im.workItemNumber
(
join [secondDB].[dbo].myPE as PE
on [secondDB].[dbo].[DG].ID = PE.ID)
WHERE
and PE.myEventID = 5
)
SELECT
[Description]
FROM fifthTable
WHERE RowNumber = 1
END
I tried fully qualifying the database names, giving different aliases, moving the joins in different orders, re-writing the syntax, creating a temporary table to do a select upon, even parametrizing the join that it's failing on to no avail
