Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

where to place CASE WHEN column IS NULL in this query

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
like image 931
ivorytux Avatar asked Sep 10 '13 14:09

ivorytux


People also ask

How do you handle NULL in a CASE Statement?

SQL offers two case abbreviations to cope with null : coalesce and nullif . Both are used like functions and do not use the keywords case , when , then , else and end .

Can we use NULL in CASE Statement SQL?

The SQL CASE Expression If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.

How do I fill a NULL column in SQL?

UPDATE [table] SET [column]=0 WHERE [column] IS NULL; Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0.

Can I use is NULL with WHERE clause?

Null values can be used as a condition in the WHERE and HAVING clauses. For example, a WHERE clause can specify a column that, for some rows, contains a null value.


1 Answers

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.

like image 56
Mosty Mostacho Avatar answered Sep 21 '22 17:09

Mosty Mostacho