I am getting an error while using this query in MySQL.

Here is the query.

select course_id, sec_id, ID,
decode(name, NULL, '−', name)
from (section natural left outer join teaches)
natural left outer join instructor
where semester='Spring' and year=2010

Here is the error Error Code: 1305. FUNCTION sql_univeristy decode does not exist

CodePudding user response：

Decode exists in oracle, mysql doesn't support it. Instead, you can use: coalesce for the same effect in your case; it returns the first non-NULL argument:

select course_id, sec_id, ID,
coalesce(name, '-')
from (section natural left outer join teaches)
natural left outer join instructor
where semester='Spring' and year=2010

CodePudding user response：

Yes, DECODE doesn't exist in mySQL. There is a similar ELT function, but not exactly the same I don't think, and also your use case doesn't really call for it. COALESCE is probably what you want, but here are four different methods:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f9d37a049fc503b1b4848b7e278f2e34

create table test (name varchar(20));
insert into test(name) values('test'), ('jim'), (null);
select * from test;

SELECT name,
  coalesce(name, '-') as method1,
  if(name is null, '-', name) as method2,
  case when name is null then '-' else name end as method3,
  elt((name is null)   1, name, '-') as method4
FROM test

COALESCE returns the first non-null argument. IF isn't ANSI compliant, but it allows conditional logic, just as CASE does (and CASE is ANSI compliant, so you can port it from one rdbms to another).

Last is ELT. That just returns the argument that matches the supplied index. So you can use IS NULL to test the name, and that will return 1 if it is and 0 if it isn't. Since ELT returns null if the index is less than 1, you have to add one to the IS NULL check. This is definitely less simple than the other options, and I wouldn't go through all this just to do a simple null check. COALESCE is the way to go if that's all you're trying to do.