CODEWITHSUNDEEP
mysql
mildlylost
mildlylost

Reputation: 57

How to place an IF statement inside of an Case expression in mySQL?

I am working on a homework question and I have to place an IF statement inside of a case statement. I can't find anything online that explains how to do this. I think I have the wrong syntax. The code goes as followed:

/*Set DB context and drop the procedure if it exists (2 lines)*/
use ap;
drop procedure if exists ch13_5;


/*Delimiter statement (1 line)*/
delimiter //

/*Create procedure statement (1 line).*/
create procedure ch13_5()

/*Begin statement (1 line)*/
begin

/*Declare 3 internal variables; 2 varchars's and 1 int (3 lines)*/
declare v_state varchar(2);
declare v_city varchar(30);
declare inv_id int;



/*Set the int variable as directed in the assignment (1 line)*/
set inv_id = 15;

/*Set the internal varchar variables using a select column_value, column_value into */
/*the appropriate variables using the where condition of invoice_id = int variable (5 lines) */
select vendor_state, vendor_city into v_state, v_city
from invoices where invoice_id = inv_id;





/*BEGIN CASE and IF-ELSEIF-ELSE CONDITIONAL*/
/*Start a CASE statement using the state variable- when it's AZ, display "Arizona vendor' (3 lines) */
case
when v_state ='AZ' then 
select 'Arizona vendor' AS v_state;
when v_state='CA' then if
v_city = 'Fresno' then select 'Fresno California vendor' as v_city;
elseif v_city = 'Oxnard' then select 'LA Metro California vendor' as v_city;
else select 'California vendor' as v_city;
SELECT 'California vendor' as v_state;
else 
select 'National vendor' AS v_state;
end case;
end if;

Thank you in advance for any help :)

Upvotes: 1

Views: 21

Answers (1)

Barmar
Barmar

Reputation: 782130

END IF needs to be inside the WHEN block that contains the IF statement.

CASE
WHEN v_state = 'AZ'
THEN SELECT 'Arizona vendor' AS v_state;
WHEN v_state = 'CA'
THEN IF v_city = 'Fresno'
     THEN SELECT 'Fresno California vendor' AS v_city;
     ELSEIF v_city = 'Oxnard'
     THEN SELECT 'LA Metro Califonia vendor' AS v_city;
     ELSE SELECT 'California vendor' AS v_city;
     END IF;
ELSE SELECT 'National vendor' AS v_state;
END CASE;

Upvotes: 2

Related Questions