May 13, 2015 at 8:11 pm
Hello!
I am relatively new to complex queries and need some assistance creating a query using a CASE in order to update columns to be either A or B. A few things about this is that I am joining tables from linked servers as well. This is the last part. Any suggestions on what I have? I execute the query and receive the error:
Incorrect syntax near the keyword 'from'.
select (select FirstName from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorFirstName,
(select LastName from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorLastName,
(select PersonID from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as SupervisorEmployeeID,
tPerson.FirstName, tPerson.LastName, tPerson.PersonID,-- tPersonJobHistAlias.BusinessCardTitle
P.LeaveCode, P.AvailBal,
Case
when P.LeaveCode = 'WHourly3-5' then 'Vacation' else
Case when P.LeaveCode= 'WHourly0-3' then 'Vacation' else
Case when P.LeaveCode = 'WSalary5+' then 'Vacation' else
case when P.LeaveCode = 'WHourly5+' then 'Vacation' else
case when P.LeaveCode = 'WSalary0-5' then 'Vacation' else
case when P.LeaveCode = 'Sick' then 'Sick'
End from Test.dbo.PREL P,
[ZZZ\XXX].HCM.dbo.tPerson inner join
[ZZZ\XXX].HCM.dbo.tPersonLocationHist tPersonJobHistAlias on (tPerson.PersonGUID = tPersonJobHistAlias.PersonGUID and tPersonJobHistAlias.PersonLocationEndDate is null)
left outer join Test.dbo.PREL P on (P.Employee = tPerson.PersonID)
where tPersonJobHistAlias.SupervisorPersonGUID =
(select tPerson.PersonGUID from [ZZZ\XXX].HCM.dbo.tPerson
where PersonID = (select HRRef from DDUPExtended where WindowsUserName = SYSTEM_USER))
Any assistance would be greatly appreciated
May 13, 2015 at 10:13 pm
Not sure if these are your only problems, but there are too many case keywords in your query, plus way too many elses. I also noted that the first two tables are not joined, but have a comma separator, which means since there is no limiting statement in the where clause, this will be a Cartesian join. Not sure that is intentional or not. I did add an else clause for anything else not handled by the case statement. As a note- I left your case statement as it was - but there is an alternate syntax which seems to fit better, and I am putting that at the end so you can see.
Try this version
select
(select FirstName from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorFirstName,
(select LastName from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorLastName,
(select PersonID from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as SupervisorEmployeeID,
tPerson.FirstName,
tPerson.LastName,
tPerson.PersonID,-- tPersonJobHistAlias.BusinessCardTitle
P.LeaveCode,
P.AvailBal,
Case
when P.LeaveCode = 'WHourly3-5' then 'Vacation' --else
when P.LeaveCode= 'WHourly0-3' then 'Vacation' --else
when P.LeaveCode = 'WSalary5+' then 'Vacation' --else
when P.LeaveCode = 'WHourly5+' then 'Vacation' --else
when P.LeaveCode = 'WSalary0-5' then 'Vacation' --else
when P.LeaveCode = 'Sick' then 'Sick'
else ''
End
from
Test.dbo.PREL P,
[ZZZ\XXX].HCM.dbo.tPerson --Did you mean to do a cartesian join here??
inner join [ZZZ\XXX].HCM.dbo.tPersonLocationHist tPersonJobHistAlias
on (tPerson.PersonGUID = tPersonJobHistAlias.PersonGUID and tPersonJobHistAlias.PersonLocationEndDate is null)
left outer join Test.dbo.PREL P
on (P.Employee = tPerson.PersonID)
where
tPersonJobHistAlias.SupervisorPersonGUID = (
select tPerson.PersonGUID from [ZZZ\XXX].HCM.dbo.tPerson
where PersonID = (select HRRef from DDUPExtended where WindowsUserName = SYSTEM_USER)
)
So for case statments, if you are always comparing the same field, there is shortcut syntax which performs better. Here is your case from above in that format. I would advise using this in the statement from above.
Case P.LeaveCode
when 'WHourly3-5' then 'Vacation'
when 'WHourly0-3' then 'Vacation'
when 'WSalary5+' then 'Vacation'
when 'WHourly5+' then 'Vacation'
when 'WSalary0-5' then 'Vacation'
when 'Sick' then 'Sick'
else ''
End
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
May 14, 2015 at 10:15 am
I did not see the Cartesian but it is causing some issues. How would you recommend rewriting this to remove it?
May 14, 2015 at 10:20 am
from
Test.dbo.PREL P, << do you mean to have this here
[ZZZ\XXX].HCM.dbo.tPerson --Did you mean to do a cartesian join here??
inner join [ZZZ\XXX].HCM.dbo.tPersonLocationHist tPersonJobHistAlias
on (tPerson.PersonGUID = tPersonJobHistAlias.PersonGUID and tPersonJobHistAlias.PersonLocationEndDate is null)
left outer join Test.dbo.PREL P << and here?
on (P.Employee = tPerson.PersonID)
May 14, 2015 at 7:26 pm
From what I see, all you need to do is to remove the first table previous to the comma. I did so below. Since they were both aliased with the P prefix, and the second one was actually joined, I figured that would be the correct one.
select
(select FirstName from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorFirstName,
(select LastName from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorLastName,
(select PersonID from [ZZZ\XXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as SupervisorEmployeeID,
tPerson.FirstName,
tPerson.LastName,
tPerson.PersonID,-- tPersonJobHistAlias.BusinessCardTitle
P.LeaveCode,
P.AvailBal,
Case
when P.LeaveCode = 'WHourly3-5' then 'Vacation' --else
when P.LeaveCode= 'WHourly0-3' then 'Vacation' --else
when P.LeaveCode = 'WSalary5+' then 'Vacation' --else
when P.LeaveCode = 'WHourly5+' then 'Vacation' --else
when P.LeaveCode = 'WSalary0-5' then 'Vacation' --else
when P.LeaveCode = 'Sick' then 'Sick'
else ''
End
from
[ZZZ\XXX].HCM.dbo.tPerson
inner join [ZZZ\XXX].HCM.dbo.tPersonLocationHist tPersonJobHistAlias
on (tPerson.PersonGUID = tPersonJobHistAlias.PersonGUID and tPersonJobHistAlias.PersonLocationEndDate is null)
left outer join Test.dbo.PREL P
on (P.Employee = tPerson.PersonID)
where
tPersonJobHistAlias.SupervisorPersonGUID = (
select tPerson.PersonGUID from [ZZZ\XXX].HCM.dbo.tPerson
where PersonID = (select HRRef from DDUPExtended where WindowsUserName = SYSTEM_USER)
)
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply