nested case statement not working
I took a "SQl for IBM DB2" class recently and decided to go through some exercises w/ SQLite. I have this SQL statement that works perfectly for all rows where DEPTNO <> 'C01'. But everyone in DEPTNO = 'C01' w/ matching JOBs gets a COMM = NULL. Why does this not work? update emp set comm = case deptno when 'A00' then (comm * 1.1) when 'D11' then (comm * 1.2) when 'C01' then case job when 'ANALYST' then (comm * 1.3) when 'MANAGER' then (comm * 1.4) else null end else comm end where comm is not null and deptno <> 'E21' ;
(2.1) By Keith Medcalf (kmedcalf) on 2021-05-23 20:33:35 edited from 2.0 in reply to 1 [link] [source]
select quote(deptno), quote(job), comm, case deptno when 'A00' then (comm * 1.1) when 'D11' then (comm * 1.2) when 'C01' then case job when 'ANALYST' then (comm * 1.3) when 'MANAGER' then (comm * 1.4) else null end else comm end as newcomm from emp where comm is not null and deptno <> 'E21' ;
And see if the results shed light ...
Thanks Keith! That helped. I had not realized that the data, actually taken from DB2 and being CHAR(8), contained trailing spaces for those with shorter lengths.
I though trailing spaces may be the issue.
You can fix that by any one of
- adding the trailing space (as in
WHEN 'ANALYST ' then)
- adding the builtin collation rtrim to the comparison (as in
WHEN 'ANALYST' collate rtrim THEN)
collate rtrim to the column definition
- updating the table to remove the trailing spaces
rtrim collation is specifically designed to ignore trailing spaces during text comparisons.
Also, don't forget about the simple TRIM() function:
... case TRIM(job) WHEN 'ANALYST' then ...
TRIM(s,c) removes any characters specified in the string "c" that surrounds (leading or trailing) the text "s". If c is not specified, then TRIM(s) removes spaces around "s".
Yes, though only adding the
collate rtrim definition to the column definition in the table will make indexes including the column "work properly" without modifying all the table data to remove the trailing space(s) ...
I know this link is to PostgreSQL's documentation, but I reckon the hint is valid for all current SQL databases, esp. SQLite:
Don't use the type char(n). You probably want text.
Any string you insert into a char(n) field will be padded with spaces to the declared width. That's probably not what you actually want.
(... explanation continues ...)
Why is this important for SQLite?