SQLite Forum

nested case statement not working
Login

nested case statement not working

(1) By SCOTT CASON (nosacs) on 2021-05-23 19:52:16 [link] [source]

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 [source]

see if:

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 ...

(3) By SCOTT CASON (nosacs) on 2021-05-27 22:29:07 in reply to 2.1 [link] [source]

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.

(4) By Keith Medcalf (kmedcalf) on 2021-05-27 23:36:31 in reply to 3 [link] [source]

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)
  • adding collate rtrim to the column definition
  • updating the table to remove the trailing spaces

The built-in rtrim collation is specifically designed to ignore trailing spaces during text comparisons.

(5) By Ryan Smith (cuz) on 2021-05-28 01:49:58 in reply to 4 [link] [source]

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".

(6) By Keith Medcalf (kmedcalf) on 2021-05-28 03:25:03 in reply to 5 [link] [source]

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) ...

(7) By Holger J (holgerj) on 2021-05-28 12:39:53 in reply to 3 [link] [source]

I know this link is to PostgreSQL's documentation, but I reckon the hint is valid for all current SQL databases, esp. SQLite:

https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29

Don't use the type char(n). You probably want text.

Why not?

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 ...)

(8) By Tim Streater (Clothears) on 2021-05-28 13:29:06 in reply to 7 [link] [source]

Why is this important for SQLite?

(9) By Gunter Hick (gunter_hick) on 2021-05-31 05:34:38 in reply to 7 [link] [source]

SQLite has no char(n) type, only text. Any trailing spaces in text field are there by design, not by any actions of SQLite.