Incorrect syntax near '.' when using Coalesce

Question

Incorrect syntax near '.' when using Coalesce

I am trying to join three tables, I have successfully joined two.

This work (two tables)

SELECT  ImportExportBadFile.ID, ImportExportSettingID,
    ImportExportBadFile.UserID,
    Coalesce(UserName,'') UserName  
    FROM
    ImportExportBadFile 
    LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID
    ORDER BY  ImportExportBadFile.DateTimeStamp DESC

Username belongs to the users table

Now that I tried to add another table:

SELECT  ImportExportBadFile.ID, ImportExportSettingID,
    SETT.Name,
    ImportExportBadFile.UserID,
    Coalesce(UserName,'') HGSQLUsers.UserName  
    FROM
    ImportExportBadFile 
    LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID
    INNER JOIN ImportExportSettings SETT ON ImportExportBadFile.ImportExportSettingID = SETT.ID  
    ORDER BY  ImportExportBadFile.DateTimeStamp DESC

It gives the error: "Incorrect syntax nearby". ". on the line:" Coalesce (UserName, '') HGSQLUsers.UserName "

If I write only UserName

He gives

"Ambiguous column name 'UserName'."

Reference.

+3

sql sql-server sql-server-2008 coalesce

SurajS May 11 '15 at 7:25

source to share

2 answers

This actually worked for me on trial and error:

SELECT  ImportExportBadFile.ID, ImportExportSettingID,
    SETT.Name,
    ImportExportBadFile.UserID,
    Coalesce(HGSQLUsers.UserName,'')
    FROM
    ImportExportBadFile 
    INNER JOIN ImportExportSettings SETT ON ImportExportBadFile.ImportExportSettingID = SETT.ID  
    LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID
    ORDER BY  ImportExportBadFile.DateTimeStamp DESC

but @Parado's answer is more specific and explanatory.

Thank.

+1

SurajS May 13 '15 @ 9:10 am

source to share

Parado · Accepted Answer · 2015-05-11T07:26:30+0000

Probably the column UserName

exists in both tables, you need to specify which table to take UserName

from as shown below

SELECT  ImportExportBadFile.ID, ImportExportSettingID,
    SETT.Name,
    ImportExportBadFile.UserID,
    Coalesce( HGSQLUsers.UserName  ,'') as "HGSQLUsers.UserName"
    FROM
    ImportExportBadFile 
    LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID
    INNER JOIN ImportExportSettings SETT ON ImportExportBadFile.ImportExportSettingID = SETT.ID
ORDER BY  ImportExportBadFile.DateTimeStamp DESC

The error "Incorrect syntax near '.'." at line :

comes from the alias HGSQLUsers.UserName

, you can use it but in quotas.

I also recommend that you use aliases for each table as shown below - more readable for me

SELECT  I.ID, I.ImportExportSettingID,
        SETT.Name,
        I.UserID,
       Coalesce( HGSQLUsers .UserName  ,'') as "HGSQLUsers.UserName"
FROM ImportExportBadFile I
LEFT OUTER JOIN Users HGSQLUsers ON I.UserID = HGSQLUsers.ID
INNER JOIN ImportExportSettings SETT ON I.ImportExportSettingID = SETT.ID  
ORDER BY  I.DateTimeStamp DESC

Incorrect syntax near '.' when using Coalesce

More articles: