Incorrect syntax near keyword 'On'

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:

SyntaxEditor Code Snippet

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:

• Give an alias to each one;
• Select the PersonnelId of the first one, so you can use <alias_name>.[PersonnelId] in the ON statement;

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:

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]

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]
    ) dpt
ON {Personnel}.[DepartmentId] = dptID