CODEWITHSUNDEEP
sqldb2case
Craig Hooghiem
Craig Hooghiem

Reputation: 1282

DB2 CASE Statement

I need to somehow use the CASE syntax (which is beyond me) to affect the database results based on criteria. I have a bunch of royalties in 0.# form (royalty) I have a title ID # (title_id) and I need to show the new increase in royalties 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 much appreciated.

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

Upvotes: 5

Views: 20149

Answers (2)

paxdiablo
paxdiablo

Reputation: 881453

Actually, you don't need to use the case statement:

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;

(under transactional control if you need atomicity). You could possibly combine the first two if they have the same multiplier as your question states.

It works by ensuring you do the higher values first and limit what rows get affected in the where clause.

If you feel you must use a case statement:

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're pulling out of the table (rather than changing the table itself) and compare it with the current:

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;

Upvotes: 9

Will Marcouiller
Will Marcouiller

Reputation: 24132

I don't know the exact DB2 syntax, neither whether it is different from Oracle or SQL Server, but I would guess something like 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 understand the question correctly. This would apply the raise for each row whenever the update is launched. You might add a where clause if you wish to perform a conditional update for each row.

EDIT

Yes, But I want to show this without altering the data, so I can show a comparison of the two numbers

Do you mean you only want to perform a select statement with the initial value in one column, and the raised value in another?

Upvotes: 0

Related Questions