DB2 CASE Statement

Question

I need to somehow use the CASE syntax (which is taller than me) to influence the database results based on criteria. I have a bunch of royalties in 0. # form (royalty) I have a caption ID # (title_id) and I need to show a new royalty increase so that I can use the data.

IF: they have a current royalty of 0.0 - 0.1 = 10% raise IF: they have 0.11 - 0.15 = 20% raise IF: royalty >= 0.16 = 20% raise 

Any help would be greatly appreciated.

  create table royalites ( title_id char(6), lorange integer, hirange integer, royalty decimal(5,2)); 

Answer 1

In fact, you do not need to use the case :

 update royalties set royalty = royalty * 1.2 where royalty >= 0.16; update royalties set royalty = royalty * 1.2 where royalty >= 0.11 and royalty < 0.16; update royalties set royalty = royalty * 1.1 where royalty < 0.11; 

(with transactional control, if you need atomicity). You could combine the first two if they have the same factor as your question.

It works by ensuring that you first execute higher values ​​and limit which lines affect the where clause.

If you think you need to use the case :

 update royalties set royalty = case when royalty >= 0.16 then royalty * 1.2 case when royalty >= 0.11 and royalty < 0.16 then royalty * 1.2 case when royalty < 0.11 then royalty * 1.1 end; 

To just change what you pull from the table (instead of changing the table itself) and compare it with the current one:

 select title_id, lorange, hirange, royalty, case when royalty >= 0.16 then royalty * 1.2 case when royalty >= 0.11 and royalty < 0.16 then royalty * 1.2 case when royalty < 0.11 then royalty * 1.1 end as new_royalty from royalties; 

Answer 2

I do not know the exact syntax of DB2, nor that it differs from Oracle or SQL Server, but I would assume the following:

 update royalties as r set r.royalty = r.royalty * ( select case when r.royalty between 0.0 and 0.1 then 1.1 when r.royalty > 0.11 then 1.2 and from royalties ) 

Something around this code could do the job if I understood the question correctly. This will apply a raise for each line every time the update starts. You can add a where clause if you want to conditionally update each line.

EDIT

Yes, but I want to show it without changing the data, so I can show a comparison of two numbers

Do you mean that you want to execute a select statement with an initial value in one column, and an increased value in another?