The use of decode in Oracle and Cognos Framework Manager

We all know the role of the decode function in Oracle and Cognos Framework Manager. Now, we will introduce how to use the Decode function in the WHERE Condition Statement. Www.2cto.com is easy to understand .. SQL code with tmp as (select 1 val, null flag from dual union all select 1 val, 'A' flag from dual union all select 2 val, 'B' flag from dual union all select 3 val, 'C' flag from dual) select 1, decode (flag, 'A', 1, 'B', 2, 'C', 3, 0) from tmp, the data we found is: 1 1 --, a 1 2 --, B 1 3 --, c. No problem. Look at this. No: SQL code with tmp as (select 1 val, null flag from dual union all select 1 val, 'A' flag from dual union all select 2 val, 'B' flag from dual union all select 3 val, 'C' flag from dual) select 1, decode (flag, 'A', 1, 'B', 2, 'C', 3, 0) from tmp where decode (val, 1, 1, 2) = decode (flag, 'A', 1, 'B', 2, 'C ', 3, 0); another way: SQL code with tmp as (select 1 val, null flag from dual union all select 1 val, 'A' flag from dua L union all select 2 val, 'B' flag from dual union all select 3 val, 'C' flag from dual) select 1, decode (flag, 'A', 1, 'B', 2, 'C', 3, 0) from tmp where (val = 1 and flag = 'A ') or (val <> 1 and flag = 'B .. In fact, decode is just a short term in this case. But for Cognos FrameworkManager, it is used to match parameters. It is used to use different filtering conditions based on different passed parameters. SQL code with tmp as (select 1 val, null flag from dual union all select 1 val, 'A' flag from dual union all select 2 val, 'B' flag from dual union all select 3 val, 'C' flag from dual) select 1, decode (flag, 'A', 1, 'B', 2, 'C', 3, 0) from tmp and decode (# prompt ('display', 'string') #, '1', 1, '2', val, '0') = decode (# prompt ('display', 'string') #, '1', 1, '2', 2, '1') to explain, when the display parameter is 1, it is not filtered. When the display parameter is 2, filter val = 2. When the display parameter is null or other values, filter out all values 0 = 1. Learn and use it .. Today, we found that case when can also be easily used: SQL code with tmp as (select 1 id, 'Kelly 'name from dual union all select 2 id, 'Sam 'name from dual union all select 3 id, 'jason 'name from dual union all select 4 id, 'kobe' name from dual union all select 5 id, null name from dual) select id, name from tmp where id = case when length (name)> 1 then length (name) else 5 end;