Oracle PL/SQL/Conversion Functions/DECODE
Содержание
- 1 Create a report that shows number of customers per state.
- 2 Decode a column with substr and instr
- 3 DECODE and GROUPING
- 4 DECODE and substr function
- 5 Decode char value to number
- 6 decode column data to output more meaningful information
- 7 Decode for more than one key value pair
- 8 DECODE in the GROUP BY clause
- 9 Decode statement could be easily rewritten using the CASE statement
- 10 Decode the result from row_number over, partition by, order by
- 11 Demo range comparison with DECODE
- 12 Extract any procedure, function or package
- 13 Format nullable heading
- 14 Syntax: DECODE
- 15 Use DECODE and SIGN functions on the ORD table
- 16 Use decode as if statement and output "high" or "low"
- 17 Use Decode as if statement to logic like "if then else"
- 18 Use decode create dynamic select statement
Create a report that shows number of customers per state.
<source lang="sql">
SQL> SQL> SQL> create table customer(
2 cust_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50) 14 );
Table created. SQL> SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1, "Allen", "Joe","J","10 Ave","London","CA","11111","1111","111", "111-1111","Big Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Ward","Sue","W","20 Ave","New York","NY","44444","4444","444", "436-4444","B Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Jason","Pure","J","50 St","Longli","CA","55555","5555","555", "234-4444","C Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Bird","Jill", null,"30 St","Pais","NY","22222","2222","222", "634-7733","D Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"Hill","Carl","H","19 Drive","A Town","CA","66666","6566","666", "243-4243","E Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Yari","P","38 Ave","Small City","NY","77777","7777","777", "454-5443","F Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Joe","Paula","J","78 St. Apt 3A","Queen City","NY","32322","2323","888", "664-4333","E Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Chili","Steve","C","38 Ave Apt 62","Mili","CA","88888","8888","787", "456-4566","G Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","M","930 Ave933","Kansas City","MO","12345","1234","412", "456-4563","H Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hack","Kisi","H","Kings Rd","Bellmore","NY","54321","3898","516", "767-5677","I Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bill","Jose","B","12 Giant Rd.","Newton","NJ","23454","1234","958", "123-7367","J Associates");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(12,"Taker","Lawrence","T","1 Sask Rd.","Camp","NJ","19191","3298","928", "123-7384","K Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(13,"Richer","Doris","R","213 Easy Street","WarPease","RI","34343","2112","501", "123-7384","L Inc");
1 row created. SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(14,"Pete","Doris","P","9 Ave","New York","NY","45454","4222","112", "123-1234","M Company");
1 row created. SQL> SQL> select state from customer; ST -- CA NY CA NY CA NY NY CA MO NY NJ NJ RI NY 14 rows selected. SQL> SQL> SELECT
2 SUM ( decode ( state, "NJ" , 1) ) as nj,
3 SUM ( decode ( state, "NY" , 1) ) as ny,
4 SUM ( decode ( state, "RI" , 1) ) as ri
5 FROM customer;
NJ NY RI
---------- ----------
2 6 1
1 row selected. SQL> SQL> SQL> drop table customer; Table dropped. SQL> --
</source>
Decode a column with substr and instr
<source lang="sql">
SQL> SQL> CREATE TABLE old_item (
2 item_id CHAR(20), 3 item_desc CHAR(25) 4 );
Table created. SQL> SQL> INSERT INTO old_item VALUES("LA-101", "Can, Small"); 1 row created. SQL> INSERT INTO old_item VALUES("LA-102", "Can, Large"); 1 row created. SQL> INSERT INTO old_item VALUES("LA-103", "Bottle, Small"); 1 row created. SQL> INSERT INTO old_item VALUES("LA-104", "Bottle, Large"); 1 row created. SQL> INSERT INTO old_item VALUES("NY-101", "Box, Small"); 1 row created. SQL> INSERT INTO old_item VALUES("NY-102", "Box, Large"); 1 row created. SQL> INSERT INTO old_item VALUES("NY-103", "Shipping Carton, Small"); 1 row created. SQL> INSERT INTO old_item VALUES("NY-104", "Shipping Carton, Large"); 1 row created. SQL> SQL> SELECT item_desc,
2 SUBSTR(item_desc, 3 INSTR(item_desc, 4 ",", 5 1 6 ) +2, 7 99 8 ) 9 FROM old_item;
ITEM_DESC SUBSTR(ITEM_DESC,INSTR(IT
-------------------------
Can, Small Small Can, Large Large Bottle, Small Small Bottle, Large Large Box, Small Small Box, Large Large Shipping Carton, Small Small Shipping Carton, Large Large 8 rows selected. SQL> SQL> drop table OLD_ITEM; Table dropped. SQL>
</source>
DECODE and GROUPING
<source lang="sql">
SQL> SQL> create table ord(
2 order_no integer primary key 3 ,cust_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created. SQL> SQL> SQL> select * from ord;
ORDER_NO CUST_NO ORDER_DATE TOTAL_ORDER_PRICE DELIVER_DATE DELIVER PA EMP_NO DELIVER_NAME
---------- -------------------- ----------------- -------------------- ------- -- ---------- -----------------------------------
GIFT_MESSAGE
1 1 14-FEB-2002 00:00:00 23 14-FEB-2002 00:00:00 12 noon CA 1
Gift for wife
2 1 14-FEB-2003 00:00:00 510.98 14-FEB-2003 00:00:00 5 pm NY 7 Rose Ted
Happy Valentines Day to Mother
3 2 14-FEB-2004 00:00:00 315.99 14-FEB-2004 00:00:00 3 pm VS 2 Ani Forest
Happy Valentines Day to Father
4 2 14-FEB-1999 00:00:00 191.95 14-FEB-1999 00:00:00 2 pm NJ 2 O. John
Happy Valentines Day
5 6 04-MAR-2002 00:00:00 101.95 05-MAR-2002 00:00:00 2:30 pm MO 2 Cora
Happy Birthday from John
6 9 07-APR-2003 00:00:00 221.95 07-APR-2003 00:00:00 3 pm MA 2 Sake Keith
Happy Birthday from Joe
7 9 20-JUN-2004 00:00:00 315.95 21-JUN-2004 00:00:00 12 noon BC 2 Jessica Li
Happy Birthday from Jessica
8 12 31-DEC-1999 00:00:00 135.95 01-JAN-2000 00:00:00 12 noon DI 3 Larry
Happy New Year from Lawrence
9 12 26-DEC-2003 00:00:00 715.95 02-JAN-2004 00:00:00 12 noon SK 7 Did
Happy Birthday from Nancy
10 4 15-JUN-2008 17:42:32 119.95 18-JUN-2008 17:42:32 6:30 pm VG 2 P. Jing
Happy Valentines Day to Jason
11 2 16-JUN-2008 17:42:33 310 18-JUN-2008 17:42:33 3:30 pm DC 2 C. Late
Happy Birthday Day to Jack
12 7 13-JUN-2008 17:42:34 121.95 14-JUN-2008 17:42:34 1:30 pm AC 2 W. Last
Happy Birthday Day to You
13 7 16-JUN-2008 17:42:35 211.95 12-JUN-2008 17:42:35 4:30 pm CA 2 J. Bond
Thanks for hard working
13 rows selected. SQL> SQL> SELECT DECODE(GROUPING(cust_no), 1,"All Custs", cust_no) AS cust_no,
2 DECODE(GROUPING(payment_method), 1, "All PMs", payment_method) AS pay_meth, 3 to_char(sum(total_order_price), "999.99") AS sales 4 FROM ord 5 WHERE cust_no IN (2,12) 6 GROUP BY CUBE (cust_no, payment_method) 7 ORDER BY 1,3 8 /
CUST_NO PAY_MET SALES
------- -------
12 DI 135.95 12 SK 715.95 12 All PMs 851.90 2 NJ 191.95 2 DC 310.00 2 VS 315.99 2 All PMs 817.94 All Custs DI 135.95 All Custs NJ 191.95 All Custs DC 310.00 All Custs VS 315.99 All Custs SK 715.95 All Custs All PMs ####### 13 rows selected. SQL> SQL> SQL> drop table ord; Table dropped. SQL> SQL> --
</source>
DECODE and substr function
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE old_item (
2 item_id CHAR(20), 3 item_desc CHAR(25) 4 );
Table created. SQL> SQL> INSERT INTO old_item VALUES("LA-101", "Can, Small"); 1 row created. SQL> INSERT INTO old_item VALUES("LA-102", "Can, Large"); 1 row created. SQL> INSERT INTO old_item VALUES("LA-103", "Bottle, Small"); 1 row created. SQL> INSERT INTO old_item VALUES("LA-104", "Bottle, Large"); 1 row created. SQL> INSERT INTO old_item VALUES("NY-101", "Box, Small"); 1 row created. SQL> INSERT INTO old_item VALUES("NY-102", "Box, Large"); 1 row created. SQL> INSERT INTO old_item VALUES("NY-103", "Shipping Carton, Small"); 1 row created. SQL> INSERT INTO old_item VALUES("NY-104", "Shipping Carton, Large"); 1 row created. SQL> SQL> SQL> SELECT DECODE(SUBSTR(item_id, 1, 2),
2 "LA", "Western", 3 "NY", "Eastern", 4 "* Unknown *" 5 ) "Region", 6 SUBSTR(item_id, 4,3) "Item ID", 7 item_desc 8 FROM old_item;
Region Ite ITEM_DESC
--- -------------------------
Western 101 Can, Small Western 102 Can, Large Western 103 Bottle, Small Western 104 Bottle, Large Eastern 101 Box, Small Eastern 102 Box, Large Eastern 103 Shipping Carton, Small Eastern 104 Shipping Carton, Large 8 rows selected. SQL> SQL> drop table OLD_ITEM; Table dropped. SQL> SQL> SQL>
</source>
Decode char value to number
<source lang="sql">
SQL> SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 );
Table created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created. SQL> SQL> CREATE OR REPLACE FUNCTION AverageGrade (
2 p_Department IN VARCHAR2,
3 p_Course IN NUMBER) RETURN VARCHAR2 AS
4
5 v_AverageGrade VARCHAR2(1);
6 v_NumericGrade NUMBER;
7 v_NumberStudents NUMBER;
8
9 CURSOR c_Grades IS
10 SELECT grade
11 FROM myStudent
12 WHERE department = p_Department
13 AND course = p_Course;
14 BEGIN
15 Debug.Reset;
16 Debug.Debug("p_Department", p_Department);
17 Debug.Debug("p_Course", p_Course);
18
19 SELECT COUNT(*)
20 INTO v_NumberStudents
21 FROM myStudent
22 WHERE department = p_Department
23 AND course = p_Course;
24
25 Debug.Debug("After select, v_NumberStudents", v_NumberStudents);
26
27 IF v_NumberStudents = 0 THEN
28 RAISE_APPLICATION_ERROR(-20001, "No students registered for " ||
29 p_Department || " " || p_Course);
30 END IF;
31
32 SELECT AVG(DECODE(grade, "A", 5,
33 "B", 4,
34 "C", 3,
35 "D", 2,
36 "E", 1))
37 INTO v_NumericGrade
38 FROM myStudent
39 WHERE department = p_Department
40 AND course = p_Course;
41
42 SELECT DECODE(ROUND(v_NumericGrade), 5, "A",
43 4, "B",
44 3, "C",
45 2, "D",
46 1, "E")
47 INTO v_AverageGrade
48 FROM dual;
49
50 RETURN v_AverageGrade;
51 END AverageGrade;
52 /
Function created. SQL> SQL> SQL> SQL> SQL> SQL> drop table myStudent; Table dropped.
</source>
decode column data to output more meaningful information
<source lang="sql">
SQL> create table gift(
2 gift_id integer primary key 3 ,emp_id integer 4 ,register_date date not null 5 ,total_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,message varchar2(100) 12 );
Table created. SQL> SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created. SQL> SQL> SQL> SQL> select gift_id, decode(payment,
2 "VS", "VISA Card Standard", 3 "VG", "VISA Gold Card", 4 "AX", "American Express", 5 "MC", "Master Card", 6 "DI", "Discover Card", 7 "CA", "Cash", 8 "CK", "Check", 9 "Unknown") 10 from gift ; GIFT_ID DECODE(PAYMENT,"VS
------------------
1 Cash
2 Cash
3 VISA Card Standard
4 Cash
5 VISA Card Standard
5 rows selected. SQL> SQL> SQL> drop table gift; Table dropped.
</source>
Decode for more than one key value pair
<source lang="sql">
SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> select * from dept;
DEPTNO DNAME LOC
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected. SQL> SQL> select dname, decode( loc, "BOSTON", "Red Sox fans",
2 "CHICAGO", "White Sox fans", 3 "DALLAS", "Astros fans", 4 "NEW YORK", "Mets fans" ) "Baseball Team" 5 from dept;
DNAME Baseball Team
--------------
ACCOUNTING Mets fans RESEARCH Astros fans SALES White Sox fans OPERATIONS Red Sox fans 4 rows selected. SQL> SQL> drop table dept; Table dropped. SQL> --
</source>
DECODE in the GROUP BY clause
<source lang="sql">
SQL> SQL> create table emp(
2 emp_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,shortZipCode varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,salary number(5,2) 14 ,birthdate date 15 ,startDate date 16 ,title varchar2(20) 17 ,dept_no integer 18 ,mgr integer 19 ,region number 20 ,division number 21 ,total_sales number 22 );
Table created. SQL> SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
2 (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");
1 row created. SQL> SQL> SQL> create or replace view avg_Sal
2 as select trunc(avg(salary)) avg_sal 3 from emp 4 /
View created. SQL> SQL> SQL> select
2 decode( sign( (salary - avg_sal ) ),
3 1, "> Average of " || to_char(avg_sal, "99.99") ,
4 0, "= Average of " || to_char(avg_sal, "99.99"),
5 -1, "< Average of " || to_char(avg_sal, "99.99") ) sal_desc,
6 count(*)
7 from emp, avg_sal
8 group by
9 decode( sign( (salary - avg_sal ) ),
10 1, "> Average of " || to_char(avg_sal, "99.99") ,
11 0, "= Average of " || to_char(avg_sal, "99.99"),
12 -1, "< Average of " || to_char(avg_sal, "99.99") )
13 /
1
1 row selected. SQL> SQL> drop table emp; Table dropped.
</source>
Decode statement could be easily rewritten using the CASE statement
<source lang="sql">
SQL> SQL> CREATE TABLE myCar
2 ( 3 MAKER VARCHAR (25), 4 MODEL VARCHAR (25), 5 PRICE NUMERIC 6 );
Table created. SQL> SQL> INSERT INTO myCar VALUES("CHRYSLER","CROSSFIRE",33620); 1 row created. SQL> INSERT INTO myCar VALUES("CHRYSLER","300M",29185); 1 row created. SQL> INSERT INTO myCar VALUES("HONDA","CIVIC",15610); 1 row created. SQL> INSERT INTO myCar VALUES("HONDA","ACCORD",19300); 1 row created. SQL> SQL> INSERT INTO myCar VALUES("FORD","MUSTANG",15610); 1 row created. SQL> INSERT INTO myCar VALUES("FORD","LATESTnGREATEST",NULL); 1 row created. SQL> INSERT INTO myCar VALUES("FORD","FOCUS",13005); 1 row created. SQL> SQL> SELECT
2 model, 3 DECODE(maker, "CHRYSLER","DOMESTIC" 4 ,"FORD","DOMESTIC" 5 ,"FOREIGN") 6 FROM myCar;
MODEL DECODE(M
--------
CROSSFIRE DOMESTIC 300M DOMESTIC CIVIC FOREIGN ACCORD FOREIGN MUSTANG DOMESTIC LATESTnGREATEST DOMESTIC FOCUS DOMESTIC 7 rows selected. SQL> SQL> SELECT
2 model, 3 CASE maker 4 WHEN "CHRYSLER" THEN "DOMESTIC" 5 WHEN "FORD" THEN "DOMESTIC" 6 ELSE "FOREIGN" 7 END 8 FROM myCar;
MODEL CASEMAKE
--------
CROSSFIRE DOMESTIC 300M DOMESTIC CIVIC FOREIGN ACCORD FOREIGN MUSTANG DOMESTIC LATESTnGREATEST DOMESTIC FOCUS DOMESTIC 7 rows selected. SQL> SQL> drop table myCar; Table dropped.
</source>
Decode the result from row_number over, partition by, order by
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20 7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30 7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00 7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20 7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30 7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00 7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20 7839.00 KING PRESIDENT 17-NOV-81 5000.00 10 7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30 7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20 7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20 7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected. SQL> SQL> SQL> set echo on SQL> SQL> select deptno,
2 max(decode(seq,1,ename,null)) highest_paid, 3 max(decode(seq,2,ename,null)) second_highest, 4 max(decode(seq,3,ename,null)) third_highest 5 from ( SELECT deptno, ename, 6 row_number() OVER 7 (PARTITION BY deptno 8 ORDER BY sal desc NULLS LAST ) seq 9 FROM emp ) 10 where seq <= 3 11 group by deptno 12 /
DEPTNO HIGHEST_PA SECOND_HIG THIRD_HIGH
---------- ---------- ----------
10 KING CLARK MILLER 20 SCOTT FORD JONES 30 BLAKE ALLEN TURNER
SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL>
</source>
Demo range comparison with DECODE
<source lang="sql">
SQL> SQL> create table emp(
2 emp_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,shortZipCode varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,salary number(5,2) 14 ,birthdate date 15 ,startDate date 16 ,title varchar2(20) 17 ,dept_no integer 18 ,mgr integer 19 ,region number 20 ,division number 21 ,total_sales number 22 );
Table created. SQL> SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
2 (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");
1 row created. SQL> SQL> create or replace view avg_Sal
2 as select trunc(avg(salary)) avg_sal 3 from emp 4 /
View created. SQL> SQL> select lastname, salary,
2 decode( sign( salary - avg_sal ), 3 1, "> Average of " || to_char(avg_sal, "99.99") , 4 0, "= Average of " || to_char(avg_sal, "99.99"), 5 -1, "< Average of " || to_char(avg_sal, "99.99") ) sal_desc 6 from emp, avg_sal 7 /
Z 1 row selected. SQL> SQL> drop table emp; Table dropped.
</source>
Extract any procedure, function or package
<source lang="sql">
SQL> -- SQL> SQL> SQL> SQL> select decode( type||"-"||to_char(line,"fm99999"),
2 "PACKAGE BODY-1", "/"||chr(10),
3 null) ||
4 decode(line,1,"create or replace ", "" ) ||
5 text text
6 from user_source
7 where name = upper("Your PackageName")
8 order by type, line;
no rows selected SQL> SQL> SQL> --
</source>
Format nullable heading
<source lang="sql">
SQL> SQL> column nullable format a10 heading "NULL|ALLOWED?" SQL> SQL> SELECT table_name, column_name,
2 DECODE(nullable, "N", "NO", "YES") nullable
3 FROM all_tab_columns
4 WHERE table_name = UPPER("&table_name");
Enter value for table_name: old 4: WHERE table_name = UPPER("&table_name") new 4: WHERE table_name = UPPER("") no rows selected
</source>
Syntax: DECODE
<source lang="sql">
SQL> SQL> CREATE TABLE myCar
2 ( 3 MAKER VARCHAR (25), 4 MODEL VARCHAR (25), 5 PRICE NUMERIC 6 );
Table created. SQL> SQL> INSERT INTO myCar VALUES("CHRYSLER","CROSSFIRE",33620); 1 row created. SQL> INSERT INTO myCar VALUES("CHRYSLER","300M",29185); 1 row created. SQL> INSERT INTO myCar VALUES("HONDA","CIVIC",15610); 1 row created. SQL> INSERT INTO myCar VALUES("HONDA","ACCORD",19300); 1 row created. SQL> SQL> INSERT INTO myCar VALUES("FORD","MUSTANG",15610); 1 row created. SQL> INSERT INTO myCar VALUES("FORD","LATESTnGREATEST",NULL); 1 row created. SQL> INSERT INTO myCar VALUES("FORD","FOCUS",13005); 1 row created. SQL> SQL> SELECT * FROM myCar; MAKER MODEL PRICE
------------------------- ----------
CHRYSLER CROSSFIRE 33620 CHRYSLER 300M 29185 HONDA CIVIC 15610 HONDA ACCORD 19300 FORD MUSTANG 15610 FORD LATESTnGREATEST FORD FOCUS 13005 7 rows selected. SQL> SQL> SELECT
2 model, 3 DECODE(maker, "CHRYSLER","DOMESTIC" 4 ,"FORD","DOMESTIC" 5 ,"FOREIGN") 6 FROM myCar;
MODEL DECODE(M
--------
CROSSFIRE DOMESTIC 300M DOMESTIC CIVIC FOREIGN ACCORD FOREIGN MUSTANG DOMESTIC LATESTnGREATEST DOMESTIC FOCUS DOMESTIC 7 rows selected. SQL> SQL> SQL> drop table myCar; Table dropped.
</source>
Use DECODE and SIGN functions on the ORD table
<source lang="sql">
SQL> SQL> SQL> SQL> SQL> create table ord(
2 order_no integer primary key 3 ,cust_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created. SQL> SQL> SQL> select
2 decode (sign (total_order_price - 30) , 3 -1 , "Small order", 4 0 , "Medium Order", 5 +1 , "Large Order" ) 6 AS size_order, 7 count(*) AS num_orders 8 from ord 9 group by 10 decode (sign (total_order_price - 30) , 11 -1 , "Small order", 12 0 , "Medium Order", 13 +1 , "Large Order" ) 14 /
SIZE_ORDER NUM_ORDERS
----------
Small order 1 Large Order 12 2 rows selected. SQL> SQL> drop table ord; Table dropped. SQL> SQL> --
</source>
Use decode as if statement and output "high" or "low"
<source lang="sql">
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select job, ename
2 , decode(greatest(sal,2500) 3 ,2500,"cheap","expensive") as class 4 from emp 5 where bdate <date "1964-01-01" 6 order by decode(job,"Designer",1,"Designer",2,3);
JOB ENAME CLASS
-------- ---------
Designer Black expensive Designer Peter expensive Tester Mary cheap Manager Mike cheap Coder Smart expensive Coder Fake expensive Tester Wil cheap Tester Jack cheap 8 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Use Decode as if statement to logic like "if then else"
<source lang="sql">
SQL> SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "KING", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 KING CLERK 7782 23-JAN-82 1300 10
14 rows selected. SQL> SQL> select decode( ename, "KING", "KING OF THE HILL", ename ) "ENAME"
2 from emp;
ENAME
SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING OF THE HILL TURNER ADAMS JAMES FORD KING OF THE HILL 14 rows selected. SQL> SQL> drop table emp; Table dropped. SQL> --
</source>
Use decode create dynamic select statement
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create or replace function get_value_static( p_empno in number, p_cname in varchar2 ) return varchar2
2 as 3 l_value varchar2(4000); 4 begin 5 select decode( upper(p_cname), 6 "ENAME", ename, 7 "EMPNO", empno, 8 "HIREDATE", to_char(hiredate,"yyyymmddhh24miss")) 9 into l_value 10 from emp 11 where empno = p_empno; 12 13 return l_value; 14 end; 15 /
Function created. SQL> SQL> SQL> SQL> drop table emp; Table dropped.
</source>
