


Let Us Learn Oracle  Part 45 of N [ Compose and Decompose function of Oracle ]
These functions came into the Oracle world from version 9i
Compose Function
Purpose: The Compose function of Oracle returns a Unicode string
Syntax: Compose(string)
SQL> Select
2 Compose('A'  UniStr('\0300'......




Let Us Learn Oracle  Part 44 of N [ First and Last function of Oracle ]
Purpose: Both of these acts as analytical and aggregate function. They atc on a set of rows that act as First or Last as per the given sorting specification.
They accepts any numeric or nonnumeric datatype that can be impli......




SELECT MatchID
2 ,MatchGroup
3 ,MatchBetween
4 ,ScheduleDate
5 ,PERCENTILE_Cont(.6) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY MatchGroup) Percentile_Cont
6 FROM MatchTable;
MATCHID ......




Total number of rows or records.
We have a set with 10 elements. We need to find the Percentile_Disc of the 7th row.Let us first find the Cumulative_distribution
So, Cumulative_Distribution = (7)/(10) = 0.7
So, Percentile_Disc =......




Let Us Learn Oracle  Part 41 of N [ Cume_Dist function of Oracle ]
It acts both as Aggregate and analytic function.
Analytic Example
Purpose: Within a group of rows, it calculates the cumulative distribution of a value in a group of values.
Syntax: Cume_Dist() Over ([partition_by_clause] ord......




Select EmpName,Salary,Ratio_to_Report(SALARY) Over() As RatioReportExample
2 From tblEmployee
3 Where DeptId = 1;
EMPNAME SALARY RATIOREPORTEXAMPLE
  
Deepak Kum......




Total number of rows or records.
e.g. We have a set with 10 elements. We need to find the relative rank of the 7th row. Henceforth, applying the values to the above formula, we get
R = 7 , N = 10
So, Percent_Rank = (71)/(101) = 0.6666666666666667
The range of values return by this function is......




It helps to further limit the effect of analytical function.
As said, the Last_Value function returns the last value from the order set of values. If the first value is null, then the function returns null.Let us see this function into action.
Example 1: Simple Last_Value demo ......




It helps to further limit the effect of analytical function.
As said, the First_Value function returns the first value from the order set of values. If the first value is null, then the function returns null.Let us see this function into action.
Example 1: Simple First_Value d......




Indicates how the data is ordered within the partition.
N.B.~Lag and Lead ......

