where to place CASE WHEN column IS NULL in this query

Question

I m having some trouble translating an MS Access query to 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 s the  CASE  part where I get stuck   CASE WHEN table3 col3 IS NULL THEN table2 col3 AS col4 ELSE table3 col3 as col4   I know the above line doesn t work  but hopefully it hints at what I m trying to accomplish  Thanks   UPDATE  All of the suggestions so far have resulted in  Incorrect syntax near the keyword  AS   error  so maybe there s something else I m missing  Below the actual query  The issue is that we have two tables  both with and EUID column  If dbo EU Admin3 EUID is not NULL  it takes precedence in the join  If dbo EU Admin3 EUID is NULL  use dbo EU Admin2 EUID instead  Hope that clarifies this   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

User · Answer

Thanks for all your help   Svetoslav Tsolov had it very close  but I was still getting an error  until I figured out the closing parenthesis was in the wrong place  Here s 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

User · Answer

Not able to understand your actual problem but your case statement is incorrect   CASE  WHEN  TABLE3 COL3 IS NULL THEN TABLE2 COL3 ELSE TABLE3 COL3 END  AS COL4

User · Answer

Try this   CASE WHEN table3 col3 IS NULL THEN table2 col3 ELSE table3 col3 END as col4   The as col4 should go at the end of the CASE the statement  Also note that you re missing the END too   Another probably more simple option would be   IIf  table3 col3  Is Null  table2 col3   table3 col3     Just to clarify  MS Access does not support COALESCE  If it would that would be the best way to go   Edit after radical question change   To turn the query into SQL Server then you can use COALESCE  so it was technically answered before too    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   BTW  your CASE statement was missing a   before the field  That s why it didn t work

User · Answer

That looks like it might belong in the 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

[sql] where to place CASE WHEN column IS NULL in this query

Examples related to sql

Examples related to ms-access

Examples related to null

Examples related to case