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

try "CASE WHEN table3.col3 IS NULL THEN table2.col3 ELSE table3.col3 END as col4" - alias (col4) is on the very end of the case — Dmitry Bychenko, Sep 10 '13 at 14:03
or the equivalent `COALESCE(table3.col3, table2.col3) AS col4` (edit: if Access supported COALESCE function) — ypercubeᵀᴹ, Sep 10 '13 at 14:13

Mosty Mostacho · Accepted Answer · 2013-09-10T15:26:41.537

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.

Just to clarify, the initial query was written in Access, but the tables have since been exported to SQL Server 2012, so I'm trying to create the equivalent of the query in SQL — ivorytux, Sep 10 '13 at 14:30

score 2 · Answer 2 · answered Sep 10 '13 at 14:04

2

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

answered Sep 10 '13 at 14:04

1

Very close, except that the closing parenthesis needs to be before "as", like so: SELECT id, col1, col2, col3, (CASE WHEN table3.col3 IS NULL THEN table2.col3 AS col4 ELSE table3.col3) as col4 END – ivorytux Sep 10 '13 at 14:52

score 2 · Answer 3 · answered Sep 10 '13 at 14:11

2

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

answered Sep 10 '13 at 14:11

Youbaraj Sharma

1,295
3
17
34

score 1 · Answer 4 · answered Sep 10 '13 at 14:55

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

where to place CASE WHEN column IS NULL in this query

4 Answers4