CODEWITHSUNDEEP
mysqlsqlcase
bennyharassi
bennyharassi

Reputation: 17

CASE WHEN to produce value based on null values

With the table CARS:

Make   | Model   | Color | Year
------- --------- ------- -----
Honda  | Accord  | Red   | 09
Nissan | Skyline | Blue  | 15
null   | Skyline | Red   | 15
Toyota | null    | Black | 15

I'm trying to produce the string ERROR in the Make column when Make contains a value but Model does not. My statement doesn't produce anything currently

SELECT 
CASE
WHEN (SELECT Count(*) 
    FROM CARS
    WHERE a.make=make
    AND a.year=year
    AND a.color=color
    AND a.model = NULL) AND (SELECT Count(*) 
    FROM CARS
    WHERE a.year=year
    AND a.color=color
    AND a.make = make) > 1 THEN '**ERROR**'
ELSE a.make
END as make
FROM a.CARS

Upvotes: 0

Views: 95

Answers (2)

jpw
jpw

Reputation: 44891

If you want to have ERROR as the value for make when make doesn't have any value, but model does then this might be what you want:

SELECT 
    CASE 
       WHEN make IS NULL AND model IS NOT NULL 
       THEN 'ERROR' 
       ELSE make 
    END AS make, 
    model, color, year 
FROM cars;

Or maybe I misunderstood your intent. Your question doesn't seem to say the same as the comment you posted to another answer.

Upvotes: 1

Drew
Drew

Reputation: 24960

create table cars
(
make varchar(100) null,
model varchar(100) null,
color varchar(100) null,
year int null
);
insert cars(make,model,color,year) values ('honda','accord','red',2009);
insert cars(make,model,color,year) values ('nissan','skyline','blue',2015);
insert cars(make,model,color,year) values (null,'skyline','red',2015);
insert cars(make,model,color,year) values ('toyota',null,'black',2015);


select 
(case when c.make is null then 'ERROR' else c.make end) as make,
(case when c.model is null then 'ERROR' else c.model end) as model,
c.color, c.year
from cars c;

Edit:

select c.make,c.model,c.color,c.year,
case when (   (c.make is null and c.model is not null) or (c.model is null and c.make is not null)    ) then 'ERROR' else '' end as col5
from cars c

Upvotes: 0

Related Questions