DECODE function SYNTAX:
-----------------------
DECODE(<selector>, <expression1>,
<result1>,
[<expression2>, <result2>],
[<expressionn>, <resultn>], [<else
result>])
Note: In DECODE selector and expression datatype should be
same, result datatype
can be different.
SELECT deptno, sal,
DECODE(DEPTNO, 10, '10% hike', 20, '20% hike', 'No hike')
SAL_HIKE
FROM EMP;
SELECT SAL, DEPTNO, DECODE(DEPTNO,10,'TEN') DEPARTMENT
FROM EMP;
SELECT EMPNO, ENAME, SAL, DEPTNO, DECODE(DEPTNO,10,1.1,
'zero') DEPARTMENT
FROM EMP;
SELECT EMPNO, ENAME, SAL, DEPTNO,
DECODE(DEPTNO,10,'Ten',TO_CHAR(deptno)) DEPARTMENT
FROM EMP;
SELECT EMPNO, ENAME, SAL, DEPTNO,
DECODE(DEPTNO,10,'TEN','Not Ten') DEPARTMENT
FROM EMP;
SELECT EMPNO, ENAME, SAL, DEPTNO,
DECODE(DEPTNO,10,'TEN',20,'TWENTY') DEPARTMENT
FROM EMP;
SELECT EMPNO, ENAME, SAL, DEPTNO,
DECODE(DEPTNO,10,'TEN',20,'TWENTY', 'No match')
DEPARTMENT FROM EMP;
SELECT * FROM EMP;
SELECT EMPNO, ENAME, SAL, DEPTNO,
DECODE(DEPTNO,10,'TEN',20,'TWENTY','others') DEPARTMENT
FROM EMP;
SELECT * FROM EMP;
SELECT EMPNO, ENAME, DECODE(ENAME,'MILLER',1,2) VL FROM emp;
SELECT * FROM EMP;
SELECT EMPNO, DECODE(ENAME,'MILLER',1), ENAME FROM EMP
ORDER BY DECODE(ENAME,'MILLER',1) DESC, ename;
SELECT EMPNO, DECODE(ENAME,'MILLER',1), ENAME FROM EMP
ORDER BY DECODE(ENAME,'MILLER',1) ASC, ename;
CASE:
CASE expression, CASE search expression
CASE expression and CASE search expression both are useful
to display the output
conditionally.
CASE expression:
Syntax:
CASE <selector> WHEN <expression1> THEN
result1
[WHEN <expression2> THEN result2]
.....................
[WHEN <expressionn> THEN resultn]
[ELSE elseresult]
END
Note: In case expression SELECTOR and expressions should
have the same datatype.
Example:
SELECT empno, DEPTNO, SAL,
CASE deptno WHEN 10
THEN SAL * 1.1
WHEN 20
THEN SAL * 1.2
ELSE SAL
END AS SAL_HIKE
FROM emp;
SELECT empno, DEPTNO, SAL,
CASE deptno WHEN 10
THEN SAL * 1.1
WHEN 20
THEN SAL * 1.2
END AS SAL_HIKE
FROM emp;
SELECT deptno, sal,
CASE deptno WHEN 10 THEN sal * 1.1 END AS dept10_hike
FROM emp;
SELECT deptno, sal,
CASE deptno WHEN
BETWEEN 10 AND 50 THEN sal * 1.1 END dept10_hike
FrOM emp;
CASE SEARCH EXPRESSION
======================
Syntax:
CASE WHEN <selector> <comparision operator>
<expression1> THEN result1
[WHEN <selector> <comparision operator>
<expression2> THEN result2]
................
[ELSE <resultn>]
END
SELECT job, comm,
CASE WHEN comm IS NULL THEN 'not earning
commission'
WHEN comm IS
NOT NULL THEN 'earning commission'
END
FROM emp;
SELECT DEPTNO, ENAME,
(CASE WHEN DEPTNO=10 THEN 'TEN'
WHEN DEPTNO=20
THEN 'TWENTY'
ELSE 'OTHERS'
END) DPT
FROM EMP;
SELECT * FROM emp;
SELECT DEPTNO, ENAME,
(CASE WHEN SAL>=3000 AND DEPTNO=10 THEN 2
WHEN DEPTNO=20
AND COMM IS NOT NULL THEN 'TWO'
END) DPT
FROM EMP;
SELECT DEPTNO, sal, comm, ENAME,
(CASE WHEN SAL>=3000 AND DEPTNO=10 THEN '2'
WHEN DEPTNO=20 AND COMM IS NOT NULL THEN 'TWO'
END) DPT
FROM EMP;
SELECT deptno, sal, job,
CASE WHEN deptno IN(10,20) THEN sal * 1.2
WHEN sal
BETWEEN 1000 AND 2500 THEN sal * 1.3
WHEN job LIKE
'%MAN' THEN sal * 1.1
ELSE sal * 1.05
END AS
salary_hike
FROM emp;
Difference between CASE Expression & CASE Search
Expression:
1.cannot perform relational operations can
perform relational expressions (>,<...)
2.single column to be used multiple
columns can be used in expr
3.single condition multiple conditions can be used
with logical operators
Difference between DECODE function and CASE search
expression:
1.DECODE is a function
1. Case is a expression
2.DECODE is a oracle proprietory 2.
CASE is sql 1999 syntax
syntax.
3.In Decode function we can 3.In
CASE expression we can compare scalar
compare scalar values only as
well as multiple values.
4.DECODE function cannot be 4.CASE
expression we can use in PLSQL statement.
used in plsql statement.
SELECT GREATEST(10,20,5) FROM DUAL;
SELECT GREATEST('a',20,'A') FROM DUAL;
SELECT ASCII('A'), ASCII('Z'), ASCII('s') FROM DUAL;
SELECT ASCII(20), ASCII('a'), ASCII('A') FROM DUAL;
2 65
32 - 1
16 - 0
8 - 0
4 - 0
2 - 0
1 - 0
01000001 -> BINARY DIGITS
8 BITS - 1 BYTE
1024 BYTE - I KB
1024 KB - 1 MB
1024 KB - 1GB
SELECT GREATEST('A','Z','s') FROM DUAL;
SELECT LEAST('A','Z','s') FROM DUAL;
SELECT LEAST(10,20,5) FROM DUAL;
SELECT ENAME, JOB FROM EMP;
SELECT ename, job, GREATEST(ename, job) from emp;
DESC emp;
No comments:
Post a Comment