Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
LAG function ?
- Thread starter Anup99999
- Start date
Can you give us a definition of the function?
Is it a database function? Steve Krandel
BASE Consulting Group
Is it a database function? Steve Krandel
BASE Consulting Group
- Thread starter
- #3
Steve,
Thanks for your interest. Yes it is a database function in Oracle 8.1.6.
Basically, the users requirement is to see various columns with respect to year (or some interval) and compare it with the previous year (or next year). This has to be shown in the same report.
One more thing....when user changes the year...the corresponding measure or data columns should also change and give data pertaining to previous year (or the next year).
The user wants to use the report for analysing the data for various time interval such that he can change year..etc..and the corresponding data..and year changes dynamically.
I hope I am clear.....if still any confusion....please let me know...I shall write an example.
Thanks in advance.
Regards,
Anup.
I don't really understand what the function is for. Can you give us a sample using SQL.
You can always build an object that is defined as LAG(table.column), but I don't think that's what you're going for here.
What you're describing sounds pretty sophisticated and is generally accomplished using multiple data providers. Steve Krandel
BASE Consulting Group
You can always build an object that is defined as LAG(table.column), but I don't think that's what you're going for here.
What you're describing sounds pretty sophisticated and is generally accomplished using multiple data providers. Steve Krandel
BASE Consulting Group
hi,
here is explaination of the fnction
but the offset is based on row and not based on any expression so your neeed of comparing values between this year and prev month cannot be achieved using LAG
as mentioned it can be achieved using data providers.
LAG/LEAD Functions
The LAG and LEAD functions are useful for comparing values when the relative
positions of rows can be known reliably. They work by specifying the count of rows
which separate the target row from the current row. Since the functions provide
access to more than one row of a table at the same time without a self-join, they can
enhance processing speed. The LAG function provides access to a row at a given
offset prior to the current position, and the LEAD function provides access to a row
at a given offset after the current position.
LAG/LEAD Syntax
The functions have the following syntax:
{LAG | LEAD}
(<value expression1>, [<offset> [, <default>]]) OVER
([PARTITION BY <value expression2>[,...]]
ORDER BY <value expression3> [collate clause>]
[ASC | DESC] [NULLS FIRST | NULLS LAST] [,...])
<offset> is an optional parameter and defaults to 1. <default> is an optional
parameter and is the value returned if the <offset> falls outside the bounds of the
table or partition.
here is explaination of the fnction
but the offset is based on row and not based on any expression so your neeed of comparing values between this year and prev month cannot be achieved using LAG
as mentioned it can be achieved using data providers.
LAG/LEAD Functions
The LAG and LEAD functions are useful for comparing values when the relative
positions of rows can be known reliably. They work by specifying the count of rows
which separate the target row from the current row. Since the functions provide
access to more than one row of a table at the same time without a self-join, they can
enhance processing speed. The LAG function provides access to a row at a given
offset prior to the current position, and the LEAD function provides access to a row
at a given offset after the current position.
LAG/LEAD Syntax
The functions have the following syntax:
{LAG | LEAD}
(<value expression1>, [<offset> [, <default>]]) OVER
([PARTITION BY <value expression2>[,...]]
ORDER BY <value expression3> [collate clause>]
[ASC | DESC] [NULLS FIRST | NULLS LAST] [,...])
<offset> is an optional parameter and defaults to 1. <default> is an optional
parameter and is the value returned if the <offset> falls outside the bounds of the
table or partition.
- Thread starter
- #6
Subhash,
Thanks very much..your answer is going to help me a lot.
I also suppose I will have to register the function in the .prm file in BO before using it. I tried to register but somehow am still not able to use it ? Can you please let me know the procedure of registering....the anlaytic functions in BO...which is different from registering normal functions.
Thanks in advance,
Best Regards,
Anup.
Thanks very much..your answer is going to help me a lot.
I also suppose I will have to register the function in the .prm file in BO before using it. I tried to register but somehow am still not able to use it ? Can you please let me know the procedure of registering....the anlaytic functions in BO...which is different from registering normal functions.
Thanks in advance,
Best Regards,
Anup.
I am also not sure how to register analytical function in prm files.
There is not need to register u can embed the sql syntax in the object definition itself.
But somehow i am not comfortable using analytical funtions in BO especially complex ones like lag , rank. As the SQL are automatically generated and functions value depend on what the user have selected it would be a daunting task to make it work.
There is not need to register u can embed the sql syntax in the object definition itself.
But somehow i am not comfortable using analytical funtions in BO especially complex ones like lag , rank. As the SQL are automatically generated and functions value depend on what the user have selected it would be a daunting task to make it work.
- Status
