SELECT empno, approx_rank ( order by approx_sum(sal) DESC ) FROM scott.emp GROUP BY empno HAVING approx_rank ( ORDER BY approx_sum(sal) DESC ) <=10 ORDER BY 2; |
|
SELECT deptno,
Approx_sum(sal), approx_rank ( order by approx_sum(sal) DESC ) FROM scott.emp GROUP BY deptno HAVING approx_rank ( ORDER BY approx_sum(sal) DESC ) <=10; |
|
SELECT deptno,
Approx_count(empno), approx_rank ( order by approx_count(empno) DESC ) FROM scott.emp GROUP BY deptno HAVING approx_rank ( ORDER BY approx_count(empno) DESC ) <=10; |
| There are many situation, where the data from external sources(like text, csv, JSON, XML etc.,) needs to be loaded into database.This can be achieved by many ways. Though there are many ways, each option is suited for particular scenarios only. This is mainly due to the difference in the source data and target data structures. For eg, we might need different mechanism to read text file vs XML files. The mechanism needs to be decided depending on the nature/type of source data. |  |
SELECT
* FROM ( SELECT job,deptno,sal FROM emp ) PIVOT ( sum ( sal ) FOR deptno IN ( 10,20,30 ) ); |  |
SELECT
* FROM ( SELECT deptno,sal FROM emp ) PIVOT ( sum ( sal ) FOR deptno IN ( 10,20,30 ) ); |  |
SELECT
* FROM ( SELECT job,sal FROM emp ) PIVOT ( sum ( sal ) FOR job IN ( 'CLERK','SALESMAN','PRESIDENT','MANAGER','ANALYST' ) ); |  |
SELECT
* FROM ( SELECT job,deptno,sal FROM emp ) PIVOT ( sum ( sal ) FOR job IN ( 'CLERK','SALESMAN','PRESIDENT','MANAGER','ANALYST' ) ); |  |
WITH
FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER
AS
BEGIN
RETURN power(p_no,3);
END;
SELECT
100 AS num,
fn_cube(10)
FROM
dual;
/
Another Example of "WITH" clause function
WITH
FUNCTION avg_sal (p_deptno NUMBER) RETURN NUMBER AS
v_avg_sal NUMBER;
BEGIN
SELECT
AVG(sal)
INTO v_avg_sal
FROM
emp
WHERE
deptno = p_deptno;
RETURN v_avg_sal;
END avg_sal;
SELECT
deptno,
dname,
loc,
round(avg_sal(deptno) ) avg_sal -- Calling the function
FROM
dept;
/
No DMLs are allowed in function while using in select statements. However DMLs can be used with autonomous_transaction functions.
CREATE TABLE temp_1 (
col1 NUMBER
);
WITH
FUNCTION avg_sal (p_deptno NUMBER) RETURN NUMBER AS
PRAGMA autonomous_transaction;
v_avg_sal NUMBER;
BEGIN
SELECT
AVG(sal)
INTO v_avg_sal
FROM
emp
WHERE
deptno = p_deptno;
INSERT INTO temp_1 VALUES ( 1 );
COMMIT;
RETURN v_avg_sal;
END avg_sal;
SELECT
deptno,
dname,
loc,
round(avg_sal(deptno) ) avg_sal -- Calling the function
FROM
dept;
/
Example of using procedure and function in WITH clause, however procedures cannot be called directly in select statements. Procedure can be accessed via the calling functions as show below.
WITH
PROCEDURE pr_cube (p_no IN OUT NUMBER)
AS
BEGIN
p_no := power(p_no,3);
END;
FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER AS
lv_num NUMBER := p_no;
BEGIN
pr_cube(lv_num);
RETURN lv_num;
END;
SELECT
100 AS num,
fn_cube(10)
FROM
dual;
/
WITH
PROCEDURE pr_cube (p_no IN OUT NUMBER) AS
PRAGMA autonomous_transaction;
BEGIN
p_no := power(p_no,3);
INSERT INTO temp_1 VALUES ( 1 );
COMMIT;
END;
FUNCTION fn_cube (p_no NUMBER) RETURN NUMBER AS
lv_num NUMBER := p_no;
BEGIN
pr_cube(lv_num);
RETURN lv_num;
END;
SELECT
100 AS num,
fn_cube(10)
FROM
dual;
/