Oracle Analytical Functions : Tutorial Part 2 Covers sum avg lag lead.

In this tutorial video we have demonstrated how to use oracle analytical functions like lag , lead , sum and avg etc.

Example 1.
Use of oracle function sum to display running totals with the use of unbounded preceding.

select sum(Salary) over (order by salary rows unbounded preceding) running_total 
, salary , e.*   from employees e

Identify gap in the contiguous sequences with the lag function.
Ex. In Employees table we have contiguous sequence of employees but due to some anomaly we found that there is now gap in the sequences. Ex Employee Id 210 comes after 206 which is not contiguous.

We can write a query as given below to identify such sequence gaps.

Oracle Lag Function

 select  *  from ( select lag (e.employee_id , 1) over (order by e.employee_id) as prev_emp,   e.* from employees e ) tmp
where (tmp.employee_id - tmp.prev_emp) > 1

Comments

How to DEBUG ORA-06502 Numeric or value error.

If you have 3000 lines of code and a custom exception handler like when others. The ORA 06502 can be notoriously elusive to find. This error is caused by . Trying to assign a higher length/precision value to variable whose size / precision is smaller than the value being assigned. Or trying to assign a non numeric value to a numeric variable. Ex . This is easy to see in 5 line code but if you have 3000+ lines of code and you did not handle the exception correctly. (plenty of times we write a generic when others handler which does not give us the line number but only a petty error message which says Numeric or valuer error.) This would be the case wherein you would need to debug or trace where the error is occurring. Here's how you can debug such failure in this case. 1. If you are on your development or test enviornment , strip off all your excption handler in your package , procedure , function whatever it is that's throwing this error. When you ru...

Oracle Database find duplicate records on certain column in a table.

This is a Beginner level article but can be referred readily by guys using Oracle Database moderately for a refernence. You can identify the duplicate records in Oracle Table by using a group by clause. You need to group by column / columns from which you are trying to identify the duplicates , use count() function to identify the duplicates : expression is self exploratory having count () > 1 . Ex 1 : Identify duplicates first names from employees table. Note in this context : Employee records is not necessarily duplicate we are only trying to find employees with same first name. select empl_first_name , count(empl_first_name) from employees group by empl_first_name having count(empl_first_name) > 1 Ex 2 : Identify duplicates or repeats based on 2 or more columns. select empl_first_name , empl_last_name , count(empl_first_name) from employees group by empl_first_name , empl_last_name having count(1) > 1 Identify duplicate / repeats based on 2 or mo...

QueryLight

Search This Blog