Hi,
First app, sorry for any silly oversights, but driven crazy by the sql query below. I'm not seeing any syntax issues. Any help is appreciated. Thanks!
SyntaxEditor Code Snippet
SELECT {Personnel}.[Id], {Department}.[Id], {Department}.[CompanyId], {Personnel}.[LastName], {Personnel}.[FirstName], {Company}.[CompanyCode], {Department}.[DeptName], {SalaryHistory}.[EffectiveDate], {SalaryHistory}.[NewSalary] FROM {Personnel} LEFT JOIN ( SELECT {SalaryHistory}.[EffectiveDate], {SalaryHistory}.[NewSalary] FROM {SalaryHistory} WHERE {SalaryHistory}.[EffectiveDate] = ( SELECT max(EffectiveDate) FROM {SalaryHistory} WHERE {SalaryHistory}.[PersonnelId] = {Personnel}.[Id] ) ) ON {Personnel}.[Id] = {SalaryHistory}.[PersonnelId] LEFT JOIN ( SELECT {Department}.[Id], {Department}.[DeptName], {Department}.[CompanyId], {Company}.[CompanyCode] FROM {Department} LEFT JOIN {Company} ON {Department}.[CompanyId] = {Company}.[Id] ) ON {Personnel}.[DepartmentId] = {Department}.[Id]
Executed SQL:
SELECT [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[ID], [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[ID], [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[COMPANYID], [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[LASTNAME], [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[FIRSTNAME], [EUT4F5047].DBO.[OSUSR_WSH_COMPANY].[COMPANYCODE], [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[DEPTNAME], [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[EFFECTIVEDATE], [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[NEWSALARY] FROM [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL] LEFT JOIN ( SELECT [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[EFFECTIVEDATE], [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[NEWSALARY] FROM [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY] WHERE [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[EFFECTIVEDATE] = ( SELECT max(EffectiveDate) FROM [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY] WHERE [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[PERSONNELID] = [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[ID] ) ) ON [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[ID] = [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[PERSONNELID] LEFT JOIN ( SELECT [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[ID], [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[DEPTNAME], [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[COMPANYID], [EUT4F5047].DBO.[OSUSR_WSH_COMPANY].[COMPANYCODE] FROM [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT] LEFT JOIN [EUT4F5047].DBO.[OSUSR_WSH_COMPANY] ON [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[COMPANYID] = [EUT4F5047].DBO.[OSUSR_WSH_COMPANY].[ID] ) ON [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[DEPARTMENTID] = [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[ID]
Hello Bill,
I believe the problem is on the two parent LEFT JOIN's where you join a result of a SELECT statement, without any alias (in both cases).
Maybe this would work:
Something like this (changes highlighted):
SELECT {Personnel}.[Id], {Department}.[Id], {Department}.[CompanyId], {Personnel}.[LastName], {Personnel}.[FirstName], {Company}.[CompanyCode], {Department}.[DeptName], {SalaryHistory}.[EffectiveDate], {SalaryHistory}.[NewSalary] FROM {Personnel} LEFT JOIN ( SELECT {SalaryHistory}.[PersonnelId], {SalaryHistory}.[EffectiveDate], {SalaryHistory}.[NewSalary] FROM {SalaryHistory} WHERE {SalaryHistory}.[EffectiveDate] = ( SELECT max(EffectiveDate) FROM {SalaryHistory} WHERE {SalaryHistory}.[PersonnelId] = {Personnel}.[Id] ) ) sh ON {Personnel}.[Id] = sh.[PersonnelId] LEFT JOIN ( SELECT {Department}.[Id], {Department}.[DeptName], {Department}.[CompanyId], {Company}.[CompanyCode] FROM {Department} LEFT JOIN {Company} ON {Department}.[CompanyId] = {Company}.[Id] ) depcomp ON {Personnel}.[DepartmentId] = depcomp.[Id]
Hi Messias,
Thanks for the assist, but that code resulted in the above error. I was avoiding alias'ing since I read in another forum post here that it gets flaky in outsystems. Yeah, I don't think it's syntax related even though that's the original error message. Perhaps I'll try another design to the query.
Take care,
Bill.
Messias Peralta wrote:
After some serious goofing around, this one actually worked...
SELECT {Personnel}.[Id], {Personnel}.[LastName], {Personnel}.[FirstName], CurrSalary.[EffectiveDate], CurrSalary.[NewSalary], dpt.DeptName, dpt.CompanyCode, dpt.dptID, dpt.cmpID FROM {Personnel}
LEFT JOIN ( SELECT PersonnelId, Effectivedate, NewSalary FROM {SalaryHistory} sh WHERE EffectiveDate = ( Select max(EffectiveDate) FROM {SalaryHistory} maxsh WHERE sh.PersonnelId = maxsh.PersonnelId ) ) CurrSalary On {Personnel}.[Id] = CurrSalary.PersonnelId
LEFT JOIN ( SELECT {Department}.[Id] dptID, {Department}.[DeptName] DeptName, {Department}.[CompanyId] cmpID, {Company}.[CompanyCode] CompanyCode FROM {Department} LEFT JOIN {Company} ON {Department}.[CompanyId] = {Company}.[Id] ) dptON {Personnel}.[DepartmentId] = dptID