MySQL CASE WHEN within CASE WHEN

Question

Cars

Make  |  Model  |  Year   |  Color  
Honda |  Accord |  12     |  Red
Lexus |    IS   |  14     |  Blue

I receive files like these and have to make the sure header row matches the parameters before I send it off to a php script.

I currently have a SELECT CASE WHEN that checks for other errors such as blanks in the code:

SELECT
CASE
WHEN a.Make = '' THEN '**BLANK**'
ELSE a.Make
END as Make,

Is it possible that there's a SELECT within SELECT that I can use to make sure the received files header (and only header) matches our headers?

You posted some sample data. What do you mean by "headers"? Tables have rows and columns. The columns are named. You mean the "column name" in a file?? or is the file it self a "header" for some detail file? — N West
– N West, Commented Jul 1, 2015 at 15:10
@KarolyHorvath I've tried this code but it runs the risk of replacing values in all rows, I just want it to replace values in row 1. — bennyharassi
– bennyharassi, Commented Jul 1, 2015 at 15:11
What you're trying to do isn't clear. I recommend editing your question to clarify exactly what you're wanting to do. It sounds like you're trying to validate your data in MySQL? You should be validating your data before it even touches the SQL. — UnknownFury
– UnknownFury, Commented Jul 1, 2015 at 15:12
Are you loading values from a text file into the database and then checking to see if some of the column entries are blank? What is supposed to happen when they are blank? How do these get sent to a php script? It might be easier to just write a small script in a language like python to do some data sanity checking before loading it into a database -- when you put data in a DB that is bad you will run into problems further down the line. — user107498
– user107498, Commented Jul 1, 2015 at 15:12

Victor111 · Accepted Answer · 2015-07-01 16:00:20Z

1

Correct me. But from what I understand, you wanted to have a record that contains the same name like your column headers.

CASE WHEN a.Make = '' THEN 'BLANK' WHEN a.Make <> 'Make' OR a.Model <> 'Model' THEN 'None' WHEN . .. .. ELSE a.Make ...

answered Jul 1, 2015 at 16:00

Victor111

588 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ankit Bajpai Over a year ago

Yupp. this can also be one solution for your prob.

Ankit Bajpai · Accepted Answer · 2015-07-01 15:16:07Z

0

I think there is no need to do so. You can just mention your conds here in this case statetment as well. Somthing like

CASE
WHEN a.Make = '' THEN '**BLANK**'
ELSE a.Make
WHEN a.Make <> '' AND  a.Make <> 'Make' THEN 'ANOTHER'
ELSE a.make
WHEN a.Model <> '' AND  a.Model <> 'Model' THEN 'ANOTHER'
ELSE a.Model
..... etc.

I think this script can work for you.

answered Jul 1, 2015 at 15:16

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

1 Comment

bennyharassi Over a year ago

I'm trying this but it looks like multiple ELSE commands in 1 CASE produce an error in MySQL

Sebastien H. · Accepted Answer · 2017-06-08 11:14:38Z

0

maybe you should try something like :

SET @make := (SELECT a.Make FROM cars a WHERE ...);
SET @model := (SELECT a.Model FROM cars a WHERE ...);

IF @make = 'Make' AND @model = 'Model' AND ... THEN
#continue your script
ELSE
#end your script
END IF;

answered Jun 8, 2017 at 11:14

Sebastien H.

7,1902 gold badges32 silver badges39 bronze badges

Collectives™ on Stack Overflow

MySQL CASE WHEN within CASE WHEN

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related