It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Useful Complex Queries

1. Ordering a result set with a column not in result set or queried tables

Order by a column that is not appear in SELECT clause:
--------------------------------------------------------

SELECT empno, ename, sal, deptno
FROM emp
ORDER BY hiredate;

Order by Expression:
----------------------

SELECT empno, ename, sal,deptno,'2' sortby
FROM emp
UNION 
SELECT empno, ename, sal,deptno,'1' sortby
FROM emp 
ORDER BY sortby;

Order by concatenated columns:
---------------------------------

SELECT first_name||' '||Last_name name, salary, deptno
FROM emp_details
order by name;

Order by column position:
---------------------------

SELECT empno, ename, sal, deptno
FROM emp
ORDER BY 1,2;

2. Find string which is not numeric:
SELECT DISTINCT SALES_ORDER FROM T_TEMPORARY_TABLE_30_6718
WHERE REGEXP_LIKE (SALES_ORDER, '[^[:digit:]]');

3.  Check previous row and make it null if same else new value will remain.
Input Record:
C1           C2           C3           C4           DT_PRV_DT
E1           P1           L1            BA1        4/1/2014
E1           P1           L2            BA1        4/2/2014
E1           P2           L2            BA1        4/3/2014
E1           P2           L2            BA2        4/4/2014
Query:
select * from TEST_DATA order by DT_PRV_DT;
select d.C1,
       decode(c2, prev_c2, null, c2) c2,
       decode(c3, prev_c3, null, c3) c3,
       decode(c4, prev_c4, null, c4) c4,
       DT_PRV_DT
  from (select C1,
               C2,
               LAG(c2) OVER(ORDER BY DT_PRV_DT) prev_c2,
               C3,
               LAG(c3) OVER(ORDER BY DT_PRV_DT) prev_c3,
               C4,
               LAG(c4) OVER(ORDER BY DT_PRV_DT) prev_c4,
               DT_PRV_DT
          from TEST_DATA) d
order by DT_PRV_DT;
Output:
C1           C2           C3           C4           DT_PRV_DT
E1           P1           L1            BA1        4/1/2014
E1                           L2                            4/2/2014
E1           P2                                           4/3/2014
E1                                           BA2        4/4/2014

4. Combine the data and get only the not NULL value for each field.
 Create table temp_shoeb (col1 varchar2(50),col2 varchar2(50),col3 varchar2(50),col4 varchar2(50),col_date date);
                COL1      COL2      COL3      COL4      COL_DATE
                val1                                        val4        2/13/2014
                val11      val22                                      2/6/2014
               val111                   val333   val444   2/18/2014
                select * from temp_shoeb order by col_date;
select *
  from (select LAST_VALUE(d.col1 IGNORE NULLS) OVER(ORDER BY d.col_dateROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS column1,
               LAST_VALUE(d.col2 IGNORE NULLS) OVER(ORDER BY d.col_dateROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS column2,
               LAST_VALUE(d.col3 IGNORE NULLS) OVER(ORDER BY d.col_dateROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS column3,
               LAST_VALUE(d.col4 IGNORE NULLS) OVER(ORDER BY d.col_dateROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS column4
          from (select * from temp_shoeb order by col_date) d)
where rownum = 1;
5. LEAD function to update values based on next column:

SELECT ri.RECORDID,
               ri.EMPLOYEE_ID, 
               ri.EFFECTIVE_FROM,
               NVL(LEAD (EFFECTIVE_FROM,1) OVER (PARTITION BY EMPLOYEE_ID ORDER BY EMPLOYEE_ID , EFFECTIVE_FROM ) -1, TO_DATE('12/31/9999','MM/DD/YYYY'))  AS next_eff_from
        FROM T_EMP_HISTORY ri 
        WHERE ri.RECORDID= 7007  
        ORDER BY ri.EMPLOYEE_ID,ri.EFFECTIVE_FROM, next_eff_from;

6. Returning values in update and insert statement

DECLARE
    id          number;
    first_name  varchar2(30);
    last_name   varchar2(30);
    start_date  varchar2(30);
    end_date    varchar2(30);
    salary      varchar2(30);
    city        varchar2(30);
    description varchar2(30);
 BEGIN

    UPDATE employee
       SET salary = salary + (salary * .025) , start_date=sysdate
     where id = 1 returning id, first_name, last_name, start_date, end_date,
     salary, city, description into id, first_name, last_name,
     start_date, end_date, salary, city, description;

    dbms_output.put_line(id || ' ' || first_name || ' ' || last_name || ' ' ||start_date || ' ' || end_date || ' ' || salary || ' ' || city || ' ' ||description);

  END;




RATIO_TO_REPORT - Analytic Functions

In Oracle PL/SQL, RATIO_TO_REPORT is an analytic function which returns the proportion of a value over the total set of values. A statement "RATIO_TO_REPORT of 2 over (1,2,3,4,5)" is (2/15) i.e. 0.133. Note that it returns NULL for NULL values of a column.


Syntax:
RATIO_TO_REPORT(expr) OVER ([ query_partition_clause ])


 It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null.
The set of values is determined by the query_partition_clause. If you omit that clause, then the ratio-to-report is computed over all rows returned by the query.
You cannot use RATIO_TO_REPORT or any other analytic function for expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr.
The SQL query below calculates the ratio of an employee's salary over the sum of salaries in his department. SELECT DEPT, EMPNO, SAL, RATIO_TO_REPORT(SAL) OVER (PARTITION BY DEPT) RATIO FROM EMPLOYEE
      DEPT      EMPNO        SAL      RATIO
---------- ---------- ---------- ----------
        10        100       2300 .479166667
        10        110       2500 .520833333
        20        120       5400 .382978723
        20        140       3400 .241134752
        20        170       5300 .375886525
        30        180       7300 .776595745
        30        130       2100 .223404255
        40        150       6400          1
        50        160       3200          1
9 rows selected.
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr
   FROM employees
   WHERE job_id = 'PU_CLERK';

LAST_NAME                     SALARY         RR
------------------------- ---------- ----------
Khoo                            3100 .223021583
Baida                           2900 .208633094
Tobias                          2800 .201438849
Himuro                          2600  .18705036
Colmenares                      2500 .179856115

You Might Also Like

Related Posts with Thumbnails

Pages