FOR cur IN (SELECT process_Id
FROM tbl_process)
LOOP
SELECT (MAX(TO_NUMBER(substr(p.old_id, instr(p.old_id, '_', -1) + 1))))
INTO new_id
FROM tbl_process p
WHERE process_Id = cur.process_Id;
IF new_id IS NULL THEN
new_id := cur.process_Id || '_1';
ELSE
new_id := new_id + 1;
new_id := cur.process_Id || '_' || new_id;
END IF;
END LOOP;
If you want a query which returns rows in column containing NULL or empty space then the using NULL alone in where clause will not return empty space rows. So the query should be altered as below
Actual Query
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL;
Altered Query
SELECT COUNT (*)
FROM TABLE
WHERE trim(COL_NAME) IS NULL
(or)
Altered Query
SELECT COUNT (*)
FROM TABLE
WHERE COL_NAME IS NULL OR
LENGTH(TRIM (COL_NAME)) = 0
select distinct ac.name,ac.line
from all_source ac
where ac.OWNER ='DBOWNERNAME' and
lower(ac.TEXT) like '%TABLE_NAME%';
FOR tbl_det IN (SELECT ts.column_1,
ts.column_2
FROM table_name ts
WHERE ts.column_3 = p_param_1)
LOOP
v_csv_col1 := v_csv_col1 || ',' || tbl_det.column_1;
END LOOP;
During Release the Scripts are executed in the Following Order
SELECT Sno,
Name,
Department,
Salary,
SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary
FROM Master_Employee
ORDER BY Department;
How this Works
PARTITION BY Department ORDER BY Sno
Partition By will first Partition by Department.Which means the Cumulative Sum will get Reset once the Department Changes.
ORDER BY Sno will make sure the Cumulative value calculated will not be swapped and stays the same for the respective rows.
Now to have a total amount for the Salary the query is as below.
SELECT Sno,
Name,
Department,
Salary
FROM Master_Employee
UNION
SELECT null,
null,
'Total',
NVL(SUM(Salary), 0)
FROM Master_Employee
ORDER BY Sno;
Total along with Cumulative Sum
SELECT Sno,
Name,
Department,
Salary,
SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary
FROM Master_Employee
UNION
SELECT null,
null,
'Total',
NVL(SUM(Salary), 0),
null
FROM Master_Employee
ORDER BY Sno;
Another wat of fetching records using sub query
SELECT Sno,
Name,
Department,
Salary,
CASE
WHEN Department = 'Total' THEN
NULL
ELSE
SUM(Salary) over(PARTITION BY Department ORDER BY Sno)
END AS Cum_Salary
FROM (SELECT Sno, Name, Department, Salary, null, 1 AS Sort_Order
FROM Master_Employee
UNION
SELECT null,
null,
'Total',
NVL(SUM(Salary), 0),
null,
2 AS Sort_Order
FROM Master_Employee)
ORDER BY Department, Sno, Sort_Order;
While Doing sort in oracle the Normal sort works differently for the following Data
CREATE TABLE TestTable(Col1 VARCHAR2(20));
insert into TestTable values('A11');
insert into TestTable values('A260');
insert into TestTable values('A10');
insert into TestTable values('A5');
A11 A260 A10 A5
SELECT *
FROM TestTable
ORDER BY col1;
Output
A10 A11 A260 A5
You may expected A5 before A10 and A11. But It would have been before A10 and A11 when it is A05. The workaround for this is as below.
SELECT * FROM TestTable ORDER BY lpad(col1,9999999);
Output
A5 A10 A11 A260
SELECT * FROM EMP;
SELECT EMPNO
,ENAME
,dept_no
,SAL
,SUM(sal) over(ORDER BY empno) AS dept_cum
FROM EMP
ORDER BY empno;
Lets group the Cumulative Sum Based on DepartmentId
SELECT EMPNO
,ENAME
,dept_no
,SAL
,SUM(sal) over(PARTITION BY dept_no ORDER BY empno) AS dept_cum
FROM EMP
ORDER BY empno;
SELECT 'Equal' FROM DUAL WHERE NVL(NULL,'') = '';
Output
SELECT 'Equal' FROM DUAL WHERE NVL(NULL,'~') = '~';
Output
Empty
While Passing Parameters to Procedure its should be
SELECT 'Equal' FROM DUAL WHERE NVL(col_name, '~') = NVL(p_param, NVL(col_name, '~'));
While sorting with date it should be converted using to_date first with the required format.
Other wise while sorting in Desc Order 31-02-2015 will come in front of 21-10-2015
SELECT * FROM tableName ORDER BY to_date(DATE_OF_BIRTH, 'DD-MM-YYYY');