Where to place the CASE WHEN NULL column in this query

Question

I am having problems translating an MS Access query into SQL:

SELECT id, col1, col2, col3 FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id LEFT OUTER JOIN table3 ON table1.id = table3.id 

so far so good, but here is the part (CASE) where I am stuck:

 CASE WHEN table3.col3 IS NULL THEN table2.col3 AS col4 ELSE table3.col3 as col4 

I know that the line above does not work, but hopefully it hints at what I'm trying to execute. Thanks!

UPDATE: all suggestions so far have led to "Incorrect syntax next to the keyword error" AS ", so maybe something else is missing there. Below the actual query. The problem is that we have two tables, as with a column , and with EUID. If dbo.EU_Admin3.EUID is not NULL, it takes precedence in the connection. If dbo.EU_Admin3.EUID is NULL, use dbo.EU_Admin2.EUID instead. Hope this clarifies.

 SELECT dbo.AdminID.CountryID, dbo.AdminID.CountryName, dbo.AdminID.RegionID, dbo.AdminID.[Region name], dbo.AdminID.DistrictID, dbo.AdminID.DistrictName, dbo.AdminID.ADMIN3_ID, dbo.AdminID.ADMIN3 (CASE WHEN dbo.EU_Admin3.EUID IS NULL THEN dbo.EU_Admin2.EUID ELSE dbo.EU_Admin3.EUID END AS EUID) FROM dbo.AdminID LEFT OUTER JOIN dbo.EU_Admin2 ON dbo.AdminID.DistrictID = dbo.EU_Admin2.DistrictID LEFT OUTER JOIN dbo.EU_Admin3 ON dbo.AdminID.ADMIN3_ID = dbo.EU_Admin3.ADMIN3_ID 

Answer 1

Try the following:

 CASE WHEN table3.col3 IS NULL THEN table2.col3 ELSE table3.col3 END as col4 

as col4 should go at the end of the CASE statement. Also note that you also lack END .

Another, possibly simpler option:

 IIf([table3.col3] Is Null,[table2.col3],[table3.col3]) 

To clarify, MS Access does not support COALESCE. If that would be the best way.

Change after a radical change in the question:

To rotate a query in SQL Server, you can use COALESCE (so this has been technically answered before):

 SELECT dbo.AdminID.CountryID, dbo.AdminID.CountryName, dbo.AdminID.RegionID, dbo.AdminID.[Region name], dbo.AdminID.DistrictID, dbo.AdminID.DistrictName, dbo.AdminID.ADMIN3_ID, dbo.AdminID.ADMIN3, COALESCE(dbo.EU_Admin3.EUID, dbo.EU_Admin2.EUID) FROM dbo.AdminID 

By the way, your CASE statement was missing before , before the field. That is why it did not work.

Answer 2

It looks like it might belong in a select statement:

 SELECT id, col1, col2, col3, (CASE WHEN table3.col3 IS NULL THEN table2.col3 AS col4 ELSE table3.col3 as col4 END) FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id LEFT OUTER JOIN table3 ON table1.id = table3.id 

Answer 3

It is impossible to understand your actual problem, but the wrong argument is your case

 CASE WHEN TABLE3.COL3 IS NULL THEN TABLE2.COL3 ELSE TABLE3.COL3 END AS COL4 

Answer 4

Thanks for your help! @ Svetoslav Tsolov had this very close, but I still got the error until I realized that the closing bracket was in the wrong place. Here is the final query that works:

 SELECT dbo.AdminID.CountryID, dbo.AdminID.CountryName, dbo.AdminID.RegionID, dbo.AdminID.[Region name], dbo.AdminID.DistrictID, dbo.AdminID.DistrictName, dbo.AdminID.ADMIN3_ID, dbo.AdminID.ADMIN3, (CASE WHEN dbo.EU_Admin3.EUID IS NULL THEN dbo.EU_Admin2.EUID ELSE dbo.EU_Admin3.EUID END) AS EUID FROM dbo.AdminID LEFT OUTER JOIN dbo.EU_Admin2 ON dbo.AdminID.DistrictID = dbo.EU_Admin2.DistrictID LEFT OUTER JOIN dbo.EU_Admin3 ON dbo.AdminID.ADMIN3_ID = dbo.EU_Admin3.ADMIN3_ID