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