SQL String Functions
1. Concat:
Concat function is used to concatenate all the inputs provided to the function.
Select concat(‘Teradata’,’for beginners’)
Result : Teradata for beginners
2.ASCII :
Returns Ascii value of the expression .
Example :
3.CHAR2HEXINT:
CHAR2HEXINT(abc) 616263
CHR:
4.OREPLACE:
SELECT OREPLACE('TD13.1 is the current version', '13.1', '14.0');
Result : TD14.0 is the current version.
5.OTRANSLATE:
• Returns source_string with every occurrence of each character in from_string replaced with the corresponding character in to_string.
• OTRANSLATE is an embedded services system function. It can be activated by invoking embedded services functions
• Expressions passed to this function must have one of the following data types: CHAR or VARCHAR
Syntax:- SELECT OTRANSLATE(' Current Year 2013 ', '3', '4')
The occurrence in source_string of the character in from_string ('3') is replaced by the character in to_string ('4').
6. LENGTH:
The LEN() function returns the length of the value in a text field.
Syntax:- SELECT LEN(column_name) FROM table_name;
Result:- Table Geography
Region_Name Store_Name
East Boston
East New York
West Los Angeles
West San Diego
Example 1:-
SELECT Length (Store_Name)
FROM Geography
WHERE Store_Name = 'Los Angeles';
Result:
Length (Store_Name)
11
Example 2
SELECT Region_Name, Length (Region_Name)
FROM Geography;
Result:
Region_Name Length (Region_Name)
East 4
East 4
West 4
West 4
7.POSITION :
The POSITION function Returns the actual position of the character which occurs first. POSITION function is ANSI standard.
Syntax:- Examples for the POSITION function
SELECT POSITION( 'u' IN 'formula'); Displays Result as '5'
SELECT POSITION( 'fo' IN 'formula'); Displays Result as '1'
SELECT POSITION( 'e' IN 'formula'); Displays Result as '0'
8. INDEX:
Teradata has an equivalent function called INDEX.
Both the POSITION and INDEX functions returns position of character's first occurrence in a string.
Examples for the INDEX function.
SELECT INDEX('formula', 'u'); Displays Result as '5'
SELECT INDEX('formula', 'mu'); Displays Result as '4'
SELECT INDEX('formula', 'e'); Displays Result as '0'
9. LOWER :
The LCASE() function converts the value of a field to lowercase.
Syntax:-
SELECT LCASE(column_name) FROM table_name;
Ex:- SELECT LOWER('TECHONTHENET.COM');
Result: 'techonthenet.com'
10. UPPER:
The UCASE() function converts the value of a field to uppercase.
Syntax:- SELECT UCASE(column_name) FROM table_name;
UPPER('Tech on the Net')
Result: 'TECH ON THE NET'
11. SUBSTRING /SUBSTR:
The Substring function in SQL is used to return a portion of string.
Syntax:-SUBSTRING( string, start_position, length )
SELECT SUBSTRING('TechOnTheNet.com', 1, 4);
Result: 'Tech'
12. INSTRNG:
Returns the index of the first occurrence of substring
Syntax:-
INSTR (str, pattern)
Table Geography
Region_Name Store_Name
East Boston
East New York
West Los Angeles
West San Diego
Example 1 (both Oracle and MySQL)
SELECT INSTR (Store_Name, 'o')
FROM Geography
WHERE Store_Name = 'Los Angeles';
Result:2
12. TRANSLATE:
The SQL TRANSLATE() function replaces a sequence of characters in a string with another sequence of characters. The function replaces a single character at a time.
Syntax:-
TRANSLATE(char_value USING translation_name)
SELECT TRANSLATE(string1, string_to_replace, replacement_string)
FROM table_name;
• String1 is the string in which sequences of characters are to be replaced
• String_to_replace is the sequence of characters to be replaced
• Replacement_string is the string of characters that will replace the string_to_replace
Example:-
SELECT TRANSLATE('Tech Honey', 'ech','abc')
FROM dual;
The output of the above statement will be:
TRANSLATE(‘TECHHONEY’,’ECH’,’ABC’)
Tabc Honay
13. TRANSLATE_OK:
14. TRIM:
The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces.
Syntax:-
TRIM( [ [LOCATION] [remstr] FROM ] str)
Example 1
SELECT TRIM(' Sample ');
Result:
'Sample'
15. LTRIM
LTRIM function removes all space characters from the left-hand side of a string.
Syntax:-LTRIM( string )
For example:
SELECT LTRIM(' TechOnTheNet.com');
Result: 'TechOnTheNet.com'
SELECT LTRIM(' TechOnTheNet.com ');
Result: 'TechOnTheNet.com
16. RTRIM:
RTRIM function removes all space characters from the right-hand side of a string.
Syntax:- RTRIM( string )
Example:-
SELECT RTRIM('TechOnTheNet.com ');
Result: 'TechOnTheNet.com'
SELECT RTRIM(' TechOnTheNet.com ');
Result: ' TechOnTheNet.com'
17. PAD:
PAD Function is used to add a character before the word specified in PAD function .
18.LPAD:
LPAD function is used to add a character in the left side of the word which is specified in the LPAD function .
Example :
19.RPAD:
RPAD function is used to add a character in the right side of the word which is specified in the RPAD function .
Example :
20. VARGRAPHIC:
Returns the VARGRAPHIC representation of the character data in character_string_expression.
Syntax:-vargraphic(character_string_expression)
Example:- Function Result
VARGRAPHIC('92abcΔ') '92abcΔ'
VARGRAPHIC('abc') 'abc'
21.NVP:
22. LENGTH:
It is used to count the characters in the string
Syntax:- SELECT LENGTH(string)
Example:- SELECT LENGTH('Teradata')
Result:-8
ORDERED ANALYTICAL FUNCTIONS :
23. CSUM :
Returns the cumulative (or running) sum of a value expression for each row in a partition, assuming the rows in the partition are sorted by the sort_expression list.
Syntax:-
SELECT CSUM( <column-name>, <sort-key> [ASC | DESC]
[, <sort-key> [ASC | DESC] ... )
FROM <table-name>
[GROUP BY <column-name> [,<column-number> ... ] ]
;
Example:-
Report the daily running sales total for product code 10 for each month of 1998.
SELECT cmonth, CSUM(sumPrice, cdate)
FROM
(SELECT a2.month_of_year,
a2.calendar_date,a1.itemID, SUM(a1.price)
FROM Sales a1, SYS_CALENDAR.Calendar a2
WHERE a1.calendar_date=a2.calendar_date
AND a2.calendar_date=1998
AND a1.itemID=10
GROUP BY a2.month_of_year, a1.calendar_date,
a1.itemID) AS T1(cmonth, cdate, sumPrice)
GROUP BY cmonth;
Grouping by month allows the total to accumulate until the end of each month, when it is then set to zero for the next month. This permits the calculation of cumulative totals for each item in the same query.
24.MAVG:
1.Used to calculate Moving average on a column.
2.The result is based on a predefined number of rows . The number of rows used for aggregation operation is called as query width.
Syntax:-
MAVG(colname, n, sortlist)
1. Example:
SELECT employeeid,salary,hiredate,MAVG(salary,3,hiredate) from employee;
Here the query width is 3.
Employeeid Salary Hiredate MAvg(Salary,3,Hiredate)
8 8000 1/1/1992 8000 Average of row 1
4 4000 1/1/1996 6000 Average of rows 1 and 2
7 7000 6/1/1996 6333.33 Average of rows 1,2 and 3
3 3000 1/1/1997 4666.67 Average of rows 2,3 and 4
6 6000 3/1/1997 5333.33 Average of rows 3,4 and 5
2 2000 1/1/1998 3666.67 "
10 10000 1/1/1998 6000 "
5 5000 2/1/1998 5666.67 "
1 1000 1/1/1999 5333.33 "
9 9000 4/1/1999 5000 "
11 11000 5/1/1999 7000 "
12 12000 8/1/1999 10666.67 "
For computation purpose current row and the preceding n-1 rows are used.
If the number of rows is less than n-1, it uses all preceding rows.
It sorts in ascending order by sortlist column(s) as the default.
25. MDIFF:
1. The Moving Difference (MDIFF) function permits a calculation of a moving difference of a specified column, based on a defined query width (n).
2. The query width determines how many rows back to count for the subtrahend (i.e. the number being subtracted).
3. If there are less than n preceding rows, a null will be generated to represent the difference because there is no value to subtract.
Rule : If the width is 3 then there will be 3 rows with NULL values for the MDIFF column
4. Syntax:
MDIFF(colname, n, sortlist)
colname = the column on which the moving sum is computed
n = the number of rows (< 4096)
sortlist = the column(s) which determine the sequencing of the rows. Default is ascending.
5. Example:
SELECT employeeid,salary,hiredate,MDIFF(salary,3,hiredate) from employee;
Employeeid Salary Hiredate MDiff(Salary,3,Hiredate)
8 8000 1/1/1992 ? NULL as there is no value to subtract
4 4000 1/1/1996 ? NULL as there is no value to subtract
7 7000 6/1/1996 ? NULL as there is no value to subtract
3 3000 1/1/1997 -5000 Its 3000 from current row minus 8000 from the 1st row.
6 6000 3/1/1997 2000 its 6000 from current row minus 4000 from the 2nd row
2 2000 1/1/1998 -5000 its 2000 from current row minus 7000 from the 3rd row
10 10000 1/1/1998 7000 "
5 5000 2/1/1998 -1000 "
1 1000 1/1/1999 -1000 "
9 9000 4/1/1999 -1000 "
11 11000 5/1/1999 6000 "
12 12000 8/1/1999 11000 "
6. The usage of MDIFF is slightly different than MAvg and MSum in that it:
• Uses current row and preceding nth row
• Value is null if there is no preceding nth row
• Sort ascending by sortlist column(s) is default
26. MLINREG :
27. MSUM:
1.Used to calculate Moving sum on a specified column.
2.The number of rows used for aggregation operation is called as query width.
3.If there are less than n preceding rows then all those are available are used for summing
Syntax:
MSUM(colname, n, sortlist)
colname = the column on which the moving sum is computed
n = the number of rows (< 4096) which will be used in the calculation including the current row.
('n' is also refered to as the 'width' of the average)
sortlist = the column(s) which determine the sequencing of the rows. Default is ascending.
• Example:
SELECT employeeid,salary,hiredate,MSUM(salary,3,hiredate) from employee;
Query width is 3.
Employeeid Salary Hiredate MSum(Salary,3,Hiredate)
8 8000 1/1/1992 8000 same as 1st row
4 4000 1/1/1996 12000 sum of rows 1 and 2
7 7000 6/1/1996 19000 sum of rows 1,2 and 3
3 3000 1/1/1997 14000 Sum of rows 2,3 and 4
6 6000 3/1/1997 16000 sum of rows 3,4 and 5
2 2000 1/1/1998 11000 "
10 10000 1/1/1998 18000 "
5 5000 2/1/1998 17000 "
1 1000 1/1/1999 16000 "
9 9000 4/1/1999 15000 "
11 11000 5/1/1999 21000 "
12 12000 8/1/1999 32000 "
• Moving Sum (MSum) follows the same rules as Moving Averages (MAvg):
• Uses current row and preceding n-1 rows.
• Uses all preceding rows if less than n-1.
• Sort ascending by sortlist column(s) is the default.
28. PERCENT_RANK :
29. QUANTILE:
Computes the quantile scores for the values in a group.
Syntax:-
QUANTILE (<partitions>, <column-name> ,<sort-key> [DESC | ASC])[QUALIFY QUANTILE (<column-name>) {< | > | = | <= | >=} <number-of-rows>]
Example:-
Because QUANTILE uses equal-width histograms to partition the specified data, it does not partition the data equally using equal height histograms. In other words, do not expect equal row counts per specified quantile. Expect empty quantile histograms when, for example, duplicate values for sort_expression are found in the data.
For example, consider the following simple SELECT statement.
SELECT itemNo, quantity, QUANTILE(10,quantity) FROM inventory;
The report might look like this.
itemNo quantity Quantile(10, quantity)
13 1 0
9 1 0
7 1 0
2 1 0
5 1 0
3 1 0
1 1 0
6 1 0
4 1 0
10 1 0
8 1 0
11 1 0
12 9 9
Because the quantile sort is on quantity, and there are only two quantity scores in the inventory table, there are no scores in the report for deciles 1 through 8.
30. RANK :
1.The rank function allows a column to be ranked either based on high or low order, against other rows in the answer set.
2.By default, the output will be sorted in descending sequence of the ranking column(This is different from the other OLAP functions as all others have ascending as the default. This in short means that highest value in the ranked column gives rank 1.
Syntax:
RANK(columnname).
where colname represents the column to be ranked and the descending sort key of the result.
• Example:
SELECT employeeid,salary,hiredate,RANK(salary) from employee;
Employeeid Salary Hiredate Rank(Salary)
12 12000 8/1/1999 1 note that by default data is sorted on salary descending.
11 11000 5/1/1999 2
10 10000 1/1/1998 3
9 9000 4/1/1999 4
8 8000 1/1/1992 5
7 7000 6/1/1996 6
6 6000 3/1/1997 7
5 5000 2/1/1998 8
4 4000 1/1/1996 9
3 3000 1/1/1997 10
2 2000 1/1/1998 11
1 1000 1/1/1999 12
• Points to note are:
• When Ranking is applied - default highest amount is low rank number.
• The default sort sequence is descending by ranking column (sales). The result will be sorted by default on the column used for ranking and default sequence is descending.
• We can change the default sorting sequence as below
SELECT employeeid,salary,hiredate,RANK(salary ASC) from employee;
Here now the data is sorted on the salary by is in ascending sequence
Employeeid Salary Hiredate Rank(Salary ASC)
1 1000 1/1/1999 1
2 2000 1/1/1998 2
3 3000 1/1/1997 3
4 4000 1/1/1996 4
5 5000 2/1/1998 5
6 6000 3/1/1997 6
7 7000 6/1/1996 7
8 8000 1/1/1992 8
9 9000 4/1/1999 9
10 10000 1/1/1998 10
11 11000 5/1/1999 11
12 12000 8/1/1999 12
31. ROWNUMBER:
32. NVL
33.NVL2
BUILT IN FUNCTIONS :
EXPRESSIONS :
CASE :
IF THEN ELSE:
A sequence of IF-THEN statements can be followed by an optional sequence of ELSEstatements, which execute when the condition is FALSE.
Syntax:-
IF condition THEN
S1;
ELSE
S2;
END IF;
Example:-
DECLARE
a number(3) := 100;
BEGIN
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/
Result:-
a is not less than 20
value of a is : 100
1. Concat:
Concat function is used to concatenate all the inputs provided to the function.
Select concat(‘Teradata’,’for beginners’)
Result : Teradata for beginners
2.ASCII :
Returns Ascii value of the expression .
Example :
3.CHAR2HEXINT:
CHAR2HEXINT(abc) 616263
CHR:
4.OREPLACE:
SELECT OREPLACE('TD13.1 is the current version', '13.1', '14.0');
Result : TD14.0 is the current version.
5.OTRANSLATE:
• Returns source_string with every occurrence of each character in from_string replaced with the corresponding character in to_string.
• OTRANSLATE is an embedded services system function. It can be activated by invoking embedded services functions
• Expressions passed to this function must have one of the following data types: CHAR or VARCHAR
Syntax:- SELECT OTRANSLATE(' Current Year 2013 ', '3', '4')
The occurrence in source_string of the character in from_string ('3') is replaced by the character in to_string ('4').
6. LENGTH:
The LEN() function returns the length of the value in a text field.
Syntax:- SELECT LEN(column_name) FROM table_name;
Result:- Table Geography
Region_Name Store_Name
East Boston
East New York
West Los Angeles
West San Diego
Example 1:-
SELECT Length (Store_Name)
FROM Geography
WHERE Store_Name = 'Los Angeles';
Result:
Length (Store_Name)
11
Example 2
SELECT Region_Name, Length (Region_Name)
FROM Geography;
Result:
Region_Name Length (Region_Name)
East 4
East 4
West 4
West 4
7.POSITION :
The POSITION function Returns the actual position of the character which occurs first. POSITION function is ANSI standard.
Syntax:- Examples for the POSITION function
SELECT POSITION( 'u' IN 'formula'); Displays Result as '5'
SELECT POSITION( 'fo' IN 'formula'); Displays Result as '1'
SELECT POSITION( 'e' IN 'formula'); Displays Result as '0'
8. INDEX:
Teradata has an equivalent function called INDEX.
Both the POSITION and INDEX functions returns position of character's first occurrence in a string.
Examples for the INDEX function.
SELECT INDEX('formula', 'u'); Displays Result as '5'
SELECT INDEX('formula', 'mu'); Displays Result as '4'
SELECT INDEX('formula', 'e'); Displays Result as '0'
9. LOWER :
The LCASE() function converts the value of a field to lowercase.
Syntax:-
SELECT LCASE(column_name) FROM table_name;
Ex:- SELECT LOWER('TECHONTHENET.COM');
Result: 'techonthenet.com'
10. UPPER:
The UCASE() function converts the value of a field to uppercase.
Syntax:- SELECT UCASE(column_name) FROM table_name;
UPPER('Tech on the Net')
Result: 'TECH ON THE NET'
11. SUBSTRING /SUBSTR:
The Substring function in SQL is used to return a portion of string.
Syntax:-SUBSTRING( string, start_position, length )
SELECT SUBSTRING('TechOnTheNet.com', 1, 4);
Result: 'Tech'
12. INSTRNG:
Returns the index of the first occurrence of substring
Syntax:-
INSTR (str, pattern)
Table Geography
Region_Name Store_Name
East Boston
East New York
West Los Angeles
West San Diego
Example 1 (both Oracle and MySQL)
SELECT INSTR (Store_Name, 'o')
FROM Geography
WHERE Store_Name = 'Los Angeles';
Result:2
12. TRANSLATE:
The SQL TRANSLATE() function replaces a sequence of characters in a string with another sequence of characters. The function replaces a single character at a time.
Syntax:-
TRANSLATE(char_value USING translation_name)
SELECT TRANSLATE(string1, string_to_replace, replacement_string)
FROM table_name;
• String1 is the string in which sequences of characters are to be replaced
• String_to_replace is the sequence of characters to be replaced
• Replacement_string is the string of characters that will replace the string_to_replace
Example:-
SELECT TRANSLATE('Tech Honey', 'ech','abc')
FROM dual;
The output of the above statement will be:
TRANSLATE(‘TECHHONEY’,’ECH’,’ABC’)
Tabc Honay
13. TRANSLATE_OK:
14. TRIM:
The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces.
Syntax:-
TRIM( [ [LOCATION] [remstr] FROM ] str)
Example 1
SELECT TRIM(' Sample ');
Result:
'Sample'
15. LTRIM
LTRIM function removes all space characters from the left-hand side of a string.
Syntax:-LTRIM( string )
For example:
SELECT LTRIM(' TechOnTheNet.com');
Result: 'TechOnTheNet.com'
SELECT LTRIM(' TechOnTheNet.com ');
Result: 'TechOnTheNet.com
16. RTRIM:
RTRIM function removes all space characters from the right-hand side of a string.
Syntax:- RTRIM( string )
Example:-
SELECT RTRIM('TechOnTheNet.com ');
Result: 'TechOnTheNet.com'
SELECT RTRIM(' TechOnTheNet.com ');
Result: ' TechOnTheNet.com'
17. PAD:
PAD Function is used to add a character before the word specified in PAD function .
18.LPAD:
LPAD function is used to add a character in the left side of the word which is specified in the LPAD function .
Example :
The following query returns the result 'yzybuilding'.
SELECT LPAD('building', 11, 'yz');
19.RPAD:
RPAD function is used to add a character in the right side of the word which is specified in the RPAD function .
Example :
The following query returns the result 'buildingyzy'.
SELECT RPAD('building', 11, 'yz');
20. VARGRAPHIC:
Returns the VARGRAPHIC representation of the character data in character_string_expression.
Syntax:-vargraphic(character_string_expression)
Example:- Function Result
VARGRAPHIC('92abcΔ') '92abcΔ'
VARGRAPHIC('abc') 'abc'
21.NVP:
22. LENGTH:
It is used to count the characters in the string
Syntax:- SELECT LENGTH(string)
Example:- SELECT LENGTH('Teradata')
Result:-8
ORDERED ANALYTICAL FUNCTIONS :
23. CSUM :
Returns the cumulative (or running) sum of a value expression for each row in a partition, assuming the rows in the partition are sorted by the sort_expression list.
Syntax:-
SELECT CSUM( <column-name>, <sort-key> [ASC | DESC]
[, <sort-key> [ASC | DESC] ... )
FROM <table-name>
[GROUP BY <column-name> [,<column-number> ... ] ]
;
Example:-
Report the daily running sales total for product code 10 for each month of 1998.
SELECT cmonth, CSUM(sumPrice, cdate)
FROM
(SELECT a2.month_of_year,
a2.calendar_date,a1.itemID, SUM(a1.price)
FROM Sales a1, SYS_CALENDAR.Calendar a2
WHERE a1.calendar_date=a2.calendar_date
AND a2.calendar_date=1998
AND a1.itemID=10
GROUP BY a2.month_of_year, a1.calendar_date,
a1.itemID) AS T1(cmonth, cdate, sumPrice)
GROUP BY cmonth;
Grouping by month allows the total to accumulate until the end of each month, when it is then set to zero for the next month. This permits the calculation of cumulative totals for each item in the same query.
24.MAVG:
1.Used to calculate Moving average on a column.
2.The result is based on a predefined number of rows . The number of rows used for aggregation operation is called as query width.
Syntax:-
MAVG(colname, n, sortlist)
1. Example:
SELECT employeeid,salary,hiredate,MAVG(salary,3,hiredate) from employee;
Here the query width is 3.
Employeeid Salary Hiredate MAvg(Salary,3,Hiredate)
8 8000 1/1/1992 8000 Average of row 1
4 4000 1/1/1996 6000 Average of rows 1 and 2
7 7000 6/1/1996 6333.33 Average of rows 1,2 and 3
3 3000 1/1/1997 4666.67 Average of rows 2,3 and 4
6 6000 3/1/1997 5333.33 Average of rows 3,4 and 5
2 2000 1/1/1998 3666.67 "
10 10000 1/1/1998 6000 "
5 5000 2/1/1998 5666.67 "
1 1000 1/1/1999 5333.33 "
9 9000 4/1/1999 5000 "
11 11000 5/1/1999 7000 "
12 12000 8/1/1999 10666.67 "
For computation purpose current row and the preceding n-1 rows are used.
If the number of rows is less than n-1, it uses all preceding rows.
It sorts in ascending order by sortlist column(s) as the default.
25. MDIFF:
1. The Moving Difference (MDIFF) function permits a calculation of a moving difference of a specified column, based on a defined query width (n).
2. The query width determines how many rows back to count for the subtrahend (i.e. the number being subtracted).
3. If there are less than n preceding rows, a null will be generated to represent the difference because there is no value to subtract.
Rule : If the width is 3 then there will be 3 rows with NULL values for the MDIFF column
4. Syntax:
MDIFF(colname, n, sortlist)
colname = the column on which the moving sum is computed
n = the number of rows (< 4096)
sortlist = the column(s) which determine the sequencing of the rows. Default is ascending.
5. Example:
SELECT employeeid,salary,hiredate,MDIFF(salary,3,hiredate) from employee;
Employeeid Salary Hiredate MDiff(Salary,3,Hiredate)
8 8000 1/1/1992 ? NULL as there is no value to subtract
4 4000 1/1/1996 ? NULL as there is no value to subtract
7 7000 6/1/1996 ? NULL as there is no value to subtract
3 3000 1/1/1997 -5000 Its 3000 from current row minus 8000 from the 1st row.
6 6000 3/1/1997 2000 its 6000 from current row minus 4000 from the 2nd row
2 2000 1/1/1998 -5000 its 2000 from current row minus 7000 from the 3rd row
10 10000 1/1/1998 7000 "
5 5000 2/1/1998 -1000 "
1 1000 1/1/1999 -1000 "
9 9000 4/1/1999 -1000 "
11 11000 5/1/1999 6000 "
12 12000 8/1/1999 11000 "
6. The usage of MDIFF is slightly different than MAvg and MSum in that it:
• Uses current row and preceding nth row
• Value is null if there is no preceding nth row
• Sort ascending by sortlist column(s) is default
26. MLINREG :
27. MSUM:
1.Used to calculate Moving sum on a specified column.
2.The number of rows used for aggregation operation is called as query width.
3.If there are less than n preceding rows then all those are available are used for summing
Syntax:
MSUM(colname, n, sortlist)
colname = the column on which the moving sum is computed
n = the number of rows (< 4096) which will be used in the calculation including the current row.
('n' is also refered to as the 'width' of the average)
sortlist = the column(s) which determine the sequencing of the rows. Default is ascending.
• Example:
SELECT employeeid,salary,hiredate,MSUM(salary,3,hiredate) from employee;
Query width is 3.
Employeeid Salary Hiredate MSum(Salary,3,Hiredate)
8 8000 1/1/1992 8000 same as 1st row
4 4000 1/1/1996 12000 sum of rows 1 and 2
7 7000 6/1/1996 19000 sum of rows 1,2 and 3
3 3000 1/1/1997 14000 Sum of rows 2,3 and 4
6 6000 3/1/1997 16000 sum of rows 3,4 and 5
2 2000 1/1/1998 11000 "
10 10000 1/1/1998 18000 "
5 5000 2/1/1998 17000 "
1 1000 1/1/1999 16000 "
9 9000 4/1/1999 15000 "
11 11000 5/1/1999 21000 "
12 12000 8/1/1999 32000 "
• Moving Sum (MSum) follows the same rules as Moving Averages (MAvg):
• Uses current row and preceding n-1 rows.
• Uses all preceding rows if less than n-1.
• Sort ascending by sortlist column(s) is the default.
28. PERCENT_RANK :
29. QUANTILE:
Computes the quantile scores for the values in a group.
Syntax:-
QUANTILE (<partitions>, <column-name> ,<sort-key> [DESC | ASC])[QUALIFY QUANTILE (<column-name>) {< | > | = | <= | >=} <number-of-rows>]
Example:-
Because QUANTILE uses equal-width histograms to partition the specified data, it does not partition the data equally using equal height histograms. In other words, do not expect equal row counts per specified quantile. Expect empty quantile histograms when, for example, duplicate values for sort_expression are found in the data.
For example, consider the following simple SELECT statement.
SELECT itemNo, quantity, QUANTILE(10,quantity) FROM inventory;
The report might look like this.
itemNo quantity Quantile(10, quantity)
13 1 0
9 1 0
7 1 0
2 1 0
5 1 0
3 1 0
1 1 0
6 1 0
4 1 0
10 1 0
8 1 0
11 1 0
12 9 9
Because the quantile sort is on quantity, and there are only two quantity scores in the inventory table, there are no scores in the report for deciles 1 through 8.
30. RANK :
1.The rank function allows a column to be ranked either based on high or low order, against other rows in the answer set.
2.By default, the output will be sorted in descending sequence of the ranking column(This is different from the other OLAP functions as all others have ascending as the default. This in short means that highest value in the ranked column gives rank 1.
Syntax:
RANK(columnname).
where colname represents the column to be ranked and the descending sort key of the result.
• Example:
SELECT employeeid,salary,hiredate,RANK(salary) from employee;
Employeeid Salary Hiredate Rank(Salary)
12 12000 8/1/1999 1 note that by default data is sorted on salary descending.
11 11000 5/1/1999 2
10 10000 1/1/1998 3
9 9000 4/1/1999 4
8 8000 1/1/1992 5
7 7000 6/1/1996 6
6 6000 3/1/1997 7
5 5000 2/1/1998 8
4 4000 1/1/1996 9
3 3000 1/1/1997 10
2 2000 1/1/1998 11
1 1000 1/1/1999 12
• Points to note are:
• When Ranking is applied - default highest amount is low rank number.
• The default sort sequence is descending by ranking column (sales). The result will be sorted by default on the column used for ranking and default sequence is descending.
• We can change the default sorting sequence as below
SELECT employeeid,salary,hiredate,RANK(salary ASC) from employee;
Here now the data is sorted on the salary by is in ascending sequence
Employeeid Salary Hiredate Rank(Salary ASC)
1 1000 1/1/1999 1
2 2000 1/1/1998 2
3 3000 1/1/1997 3
4 4000 1/1/1996 4
5 5000 2/1/1998 5
6 6000 3/1/1997 6
7 7000 6/1/1996 7
8 8000 1/1/1992 8
9 9000 4/1/1999 9
10 10000 1/1/1998 10
11 11000 5/1/1999 11
12 12000 8/1/1999 12
31. ROWNUMBER:
32. NVL
33.NVL2
BUILT IN FUNCTIONS :
EXPRESSIONS :
CASE :
IF THEN ELSE:
A sequence of IF-THEN statements can be followed by an optional sequence of ELSEstatements, which execute when the condition is FALSE.
Syntax:-
IF condition THEN
S1;
ELSE
S2;
END IF;
Example:-
DECLARE
a number(3) := 100;
BEGIN
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/
Result:-
a is not less than 20
value of a is : 100
0 Comments