I'm actually a big fan of using analytical functions instead of using SUB-Selects or custom PL/SQL functions.
The reason is quite simple:
You save a lot of SQL executing time.
Another positive side is:
The amount of code lines is also less then the other two solutions would need.
Negativ aspect:
You need to understand the logic behind analytical functions and you need to practice with them. :)
What was my problem?
I had some incomplete data to fix. Some rows of one column in my table were not filled. For my luck I did know that the previous row included the right value.
Here the example:
The more rows you have in the table the bigger will be the difference in execution time.
If you want to know more about the LAG function.
Try this link:
http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php
The reason is quite simple:
You save a lot of SQL executing time.
Another positive side is:
The amount of code lines is also less then the other two solutions would need.
Negativ aspect:
You need to understand the logic behind analytical functions and you need to practice with them. :)
What was my problem?
I had some incomplete data to fix. Some rows of one column in my table were not filled. For my luck I did know that the previous row included the right value.
Here the example:
/* Using the LAG function */
select OE2.ID,
OE2.CAR_NO,
CASE WHEN OE2.CAR_NO IS NULL THEN
LAG(OE2.CAR_NO, 1, 0) OVER (ORDER BY OE2.ID)
ELSE OE2.CAR_NO END as CAR_NO_FIXED
from TBL_ORDER_LIST OE2
/* Using the SUB-Select */
select OE2.ID,
OE2.CAR_NO,
CASE
WHEN OE2.CAR_NO IS NULL THEN ( SELECT OE1.CAR_NO
FROM TBL_ORDER_LIST OE1
WHERE OE2.ID = OE1.ID-1
)
ELSE OE2.CAR_NO END AS CAR_NO_FIXED
from TBL_ORDER_LIST OE2
The more rows you have in the table the bigger will be the difference in execution time.
If you want to know more about the LAG function.
Try this link:
http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php
