The use of decode in Oracle and Cognos Framework Manager

Source: Internet
Author: User


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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.