If-then-else Logic in decode of oracle Rookie Learning

If-then-else Logic in decode of oracle Rookie Learning

Decode is similar to if-then-else

Syntax:

Decode (values,if1,then1,if2,then2,...else)

Values represents any column of any type of a table or any result obtained by calculation. When each value value is tested, if the value of value is the result of the if1,decode function is then1, if it does not match, then else is returned.

Here if, then, and else can all be functions or evaluation expressions.

Examples: create table student (id number,name varchar2 (4), sex number); insert into student values (1); insert into student values (2); insert into student values (3); SQL > select * from student ID NAME SEX- 1 A 1 2 B 2 3 C 1SQL >

Problem: sex column, 1 displayed as nan,2, displayed as nv

SQL > select id,name,decode (sex,1,'nan','nv') from student; ID NAME DECODE (SE- 1 A nan 2 B nv 3 C nanSQL > sex-values1-if1nan-then1nv-else experiment

Create a table:

Create table sales (month number (3), sales_tv number (4), sales_computer number (4)); insert into sales values (01Magi 10, 18); insert into sales values (02, 28, 20); insert into sales values (03, 36, and 33)

Month: month sales_tv: sales of TV sets sales_computer: sales of computers

Question:

Select the maximum monthly sales volume and use the function sign.

The sign function can determine whether the parameter is positive or negative. Positive returns 1, negative returns-1 and 0 returns 0.

SQL > select month,decode (sign (SALES_TV-SALES_COMPUTER), 1Magnum Salesale TVJ 0meme Salesalee TVrecinct 1meme Saleshell computer) as max from sales; MONTH MAX--1 18 2 28 3 36SQL >

Original: www.arppinging.com