Conversion of Oracle DECODE to MySQL CASE

Please, note, that Ispirer SQLWays 6.0 is no longer supported and provided to clients.

You can try out automated conversion of databases and applications with Ispirer Toolkit for free. Download free trial.

Check out the relevant toolkit documentation.

Ispirer Oracle to MySQL Migration overview.

Ispirer SQLWays Database Migration Software
Conversion of Oracle DECODE to MySQL CASE

The Oracle DECODE function is used to compare the specified expression to each search value one by one and return the corresponding result.

MySQL also supports the DECODE function, but it is used to decode the encrypted strings using the specified password. The MySQL CASE expression is an equivalent of the Oracle DECODE function.

SQLWays converts the Oracle DECODE function to the MySQL CASE expression.

Example:
TABLE 34. Conversion of Oracle DECODE to MySQL CASE
Oracle

MySQL
create procedure ora_sp_decode2 (job_level out 
varchar2) as
begin 
select DECODE(job_lvl,1,'level 1',2,'level 2','Unknown 
level') into job_level from employee where job_id>0;
end; 
create procedure ora_sp_decode2 (out job_level TEXT ) 
begin 
select CASE job_lvl
WHEN 1 THEN 'level 1
WHEN 2 THEN 'level 2'
ELSE 'Unknown level' end
into job_level from employee where job_id>0   ;
end ; 