Comparing with LAG Analytic function

Question

I am using oracle PL/SQL.

I am trying to compare column values with LAG function.

Following is the statement:

decode(LAG(col1,1) OVER (ORDER BY col3),col1,'No Change','Change_Occured') Changes

As for first row, LAG will always compare with the previous empty row. So for my query the first row of column 'Changes' is always showing the value as Change_Occured when in fact no change has happened. Is there any way to handle this scenario ?

Assume this table:

| col1 | col2 | 
|  2   | 3    |
|  2   | 6    |
|  2   | 7    |
|  2   | 9    |

Each row of col1 is compared with previous value so result will be

| col1 | col2 | Changes        |
|  2   | 3    | Change_occured |
|  2   | 9    | No Change      |
|  2   | 5    | No Change      |
|  2   | 8    | No Change      |

So how should I handle the first row of column Changes

Answer 1

The syntax for LAG Analytic function is:

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

• default - The value returned if the offset is outside the scope of the window. The default value is NULL.

SQL> WITH sample_data AS(
  2  SELECT 2 col1, 3 col2 FROM dual UNION ALL
  3  SELECT 2 col1, 6 col2 FROM dual UNION ALL
  4  SELECT 2 col1, 7 col2 FROM dual UNION ALL
  5  SELECT 2 col1, 9 col2 FROM dual
  6  )
  7  -- end of sample_data mimicking real table
  8  SELECT col1, LAG(col1,1) OVER (ORDER BY col2) changes FROM sample_data;

      COL1    CHANGES
---------- ----------
         2
         2          2
         2          2
         2          2

Therefore, in the DECODE expression you are comparing the NULL value with a real value and it is evaluated as Change_Occurred

You could use the default value as the column value itself:

DECODE(LAG(col1,1, col1) OVER (ORDER BY col2),col1,'No Change','Change_Occured') Changes

For example,

SQL> WITH sample_data AS(
  2  SELECT 2 col1, 3 col2 FROM dual UNION ALL
  3  SELECT 2 col1, 6 col2 FROM dual UNION ALL
  4  SELECT 2 col1, 7 col2 FROM dual UNION ALL
  5  SELECT 2 col1, 9 col2 FROM dual
  6  )
  7  -- end of sample_data mimicking real table
  8  SELECT col1,
  9         DECODE(
 10                LAG(col1,1, col1) OVER (ORDER BY col2),
 11                col1,
 12                'No Change',
 13                'Change_Occured'
 14               ) Changes
 15  FROM sample_data;

      COL1 CHANGES
---------- --------------
         2 No Change
         2 No Change
         2 No Change
         2 No Change

SQL>