CODEWITHSUNDEEP
oracle-database
Atul
Atul

Reputation: 1

Create Oracle Function by checking increment value

How to check in Oracle Function whether value is increasing in particular field for last 3 year or not. Suppose one table is company and if we want to consider only values if profit of past 3 year of company is in increasing manner.

Upvotes: 0

Views: 399

Answers (1)

Bharat
Bharat

Reputation: 6866

SELECT empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY id) NEXT_SAL,
CASE WHEN sal <= LEAD(sal, 1, MAX(SAL)) OVER (PARTITION BY dept ORDER BY sal) 
     THEN 'OK'
     ELSE 'NOT OK'
END STATUS
FROM emp
ORDER BY deptno, sal DESC;

Here if you get all status as 'OK', then value is in incrementing order, else there is some decrement.

LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row. The general syntax of LEAD is shown below:

LEAD (sql_expr, offset, default) OVER (analytic_clause)

  1. sql_expr is the expression to compute from the leading row.
  2. offset is the index of the leading row relative to the current row. offset is a positive integer with default 1.
  3. default is the value to return if the offset points to a row outside the partition range.

Upvotes: 1

Related Questions