Oracle SQL operations to derive new column value from existing columns using analytical or other functions? I’ve describedd the desired result

I have three columns ELEMENT, START_POSITION & FIELD_LENGTH and trying to derive column SKIPPED(new column) without using any functions.

Example:

ELEMENT  START_POSITION    FIELD_LENGTH 
     
  A            4                9
  B           14               10
  C           24               10
  D           36                8

Using these two columns I try to derive column named SKIPPED.

Logical Explanation:

First row contains, ELEMENT ‘A’ & START_POSITION as 4, which means A should start from 4th position, so SKIPPED value should be 3. Since FIELD_LENGTH for ‘A’ (first row) is 9 it should end in 12th position

Second row has ELEMENT ‘B’, START_POSITION as 14, WHICH MEANS SKIPPED should be 1, because ELEMENT ‘A’ ends in 12th position and ELEMENT ‘B’s START_POSITION is 14, i.e 13th position is skipped, so START_POSITION is 14

Similarly, third row has ELEMENT ‘C’ and START_POSITION as 24, because as ELEMENT ‘B’ starts in 14th position and has FIELD_LENGTH as 10, so it (ELEMENT B) ends in 23rd position. C has SKIPPED value ‘0’ because, it immediately starts after 23rd position and has START_POSITION as 24

Based on the above mentioned logic I need to derive the values of SKIPPED from START_POSITION and FIELD_LENGTH for the ELEMENT.

Similarly also for ELEMENT ‘D’

My goal is to get the below output.

ELEMENT  START_POSITION    FIELD_LENGTH  SKIPPED

  A            4                9          3
  B           14               10          1
  C           24               10          0
  D           36                8          2

Answer

You want lag() and arithmetic:

select t.*,
       (start_position -
        lag(start_position + field_length, 1, 1) over (order by start_position)
       ) as skipped
from t;

This uses the 3-argument form of lag(). The third argument is the default value.

Advertisement

Answer