August 12, 2013 at 2:28 pm
Hi,
I have this query that is giving me a
Incorrect syntax near the keyword 'SELECT' error
It's referring to the very last 'SELECT' at the bottom.
;WITH cte AS
(
select * from (SELECT
rtrim(eepNameLast) +
', ' + rtrim(eepNameFirst) +
' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,
eepNameLast AS [Last Name],
IsNull(eepNameSuffix,'') AS [Suffix],
eepNameFirst AS [First Name],
IsNull(eepNameMiddle,'') AS [Middle Name],
pehCurAmt AS [Current Amount],
pehCurHrs AS [Current Hours],
pehCoID AS [Company ID],
pehEEID AS [EE ID],
pehEmpNo AS [Emp No],
pehLocation AS [Location],
pehJobCode AS [Job Code],
pehOrgLvl1 AS [Org Level 1],
pehOrgLvl2 AS [Org Level 2],
pehOrgLvl3 AS [Org Level 3],
pehOrgLvl4 AS [Org Level 4],
pehPayGroup AS [Pay Group],
pehProject AS [Project],
pehShfShiftAmt AS [Shift Amount],
pehearncode AS [Earn Code],
pehIsVoided AS [IS Voided],
pehIsVoidingRecord AS [Voiding Record],
pehIsOvertime AS [Is Overtime]
FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID
right outer join WSISQL4.DASHBOARD.DBO.[OVERTIME Contract Percentage]
ON [org level 2] = pehOrgLvl2
--right outer join
--WSISQL4.DASHBOARD.DBO.[OVERTIME Contract Percentage] ob
--on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]
where pehPerControl > '201301011'
AND pehearncode = '0001'
)
SELECT *
,SUM([Current Hours])
OVER (PARTITION BY [Org Level 2]) AS [Org 2 Group]
FROM cte
order by [current hours]
Works fine without the CTE part. For the life of me can't figure out what I'm doing wrong here.
August 12, 2013 at 2:40 pm
It looks to me that you don't have a closing parenthesis for the cte. Not sure why you have the sub-select in the cte either, but you'll have to alias that to get it to work correctly as well. That or just use that as your select in the cte and skip the sub-select all together.
August 12, 2013 at 2:46 pm
Yep, you are right. Many thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply