Skip to main content

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.
oracle analytical function sum

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 analytical function Lag
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
Labels:

Comments

Post a Comment

Please leave your relevant comments and questions only.

Popular posts from this blog

Use the Microsoft Visual Studio Code(VScode) with Java Maven project.

The VS Code is the go to editor now a days for the latest technologies and scripting options. VS Code as we all know works seamlessly with Python and JavaScripts, and offcourse the .NET family. But VS Code has many extensions which makes it big Java IDEs run for their money. Lets explore what we could do with VSCode if we are Java developer. Install VScode , no brainer. Jus type download Visual Studio code in google. Alrright once we have VSCode. Open it. Install Maven and Java extension. 2. Go to files and a small window will now appear on left side navigation bar. 3. Select New Maven project. 4. Select the archtype from dropdown. 5. As usual input the grpid, artifactid etc. 6. Done. Go through a video which has much detailed navigation flow. Till next time !!!
Post a Comment
Read more

New version of QueryLight is out !!

Here are the fixes and new development done. Latest Update and Fixes Added ability to write xlsx in new office xlsx format. Added ability to view code with F4. Added additional describe capabilities with F4. Download Page :  Go to Project HomePage https://sourceforge.net/projects/query-light-light-orcl-client/
Post a Comment
Read more

Oracle Procedures to delete the parent child table rows with integrity constraints.

With some very hard work and innovative thinking we came up with the procedures (can be easily clubbed into package) to delete the parent child relationship row with any number of levels. Below code would find the child row of the parent row being deleted and delete it first , if the child row has its own child then that childs children are fetched and deleted so its basically a recursive algorithm There are not many situations where you would want to use this. We had a customer mstr record to be deleted which had 20 levels of child records held tight by (ORA-02292: integrity constraint   violated - child record found ) with their parent child constraints. Since this only was a delete for 1 Master record we went ahead and used this script. If you have more than 20 levels Wise course would be to disable constraints and really think why you want to delete such master record. Note of caution : BE CAREFUL using this on production environment. The procedures create the insert ...
Post a Comment
Read more