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;
--------------------------------------------------------
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;
