1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
Aggregate Functions:COVAR_POP ( expr1 , expr2 )=Get the population covariance of a set of pairs.<P>\n\nPairs where either <I>expr1</I> or <I>expr2</I> are eliminated first. Then the following\ncalculation is performed:\n\n<PRE>\n(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n\n</PRE>\n\nThe function returns a number, if applied to an empty set NULL is returned.<P>\n<B>Example:</B>\n<PRE>\nSELECT itemid,COVAR_POP(amount,profit) FROM saleitems GROUP BY itemid;\n\n ITEMID COVAR_POP(AMOUNT,PROFIT)\n---------- ------------------------\n 1 84000\n 2 5062.5\n 3 325000\n 4 4111.11111\n</PRE>
Aggregate Functions:COVAR_SAMP ( expr1 , expr2 )=Get the sample covariance of a set of pairs.<P>\n\nPairs where either <I>expr1</I> or <I>expr2</I> are eliminated first. Then the following\ncalculation is performed:\n\n<PRE>\n(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / (n - 1)\n</PRE>\n\nThe function returns a number, if applied to an empty set NULL is returned.<P>\n<B>Example:</B>\n<PRE>\nSELECT itemid,COVAR_SAMP(amount,profit) FROM saleitems GROUP BY itemid;\n\n ITEMID COVAR_POP(AMOUNT,PROFIT)\n---------- ------------------------\n 1 84000\n 2 5062.5\n 3 325000\n 4 4111.11111\n</PRE>
Aggregate Functions:GROUPING ( expr )=This function is only usefull in select statements with a group by extension such as <CODE>ROLLUP</CODE>\nor <CODE>CUBE</CODE>. These function generate extra rows with nulls which is the group by aggregate.\nThis function can be use to distinguish these rows from rows that are actually null.<P>\nThe <I>expr</I> must match a group by expression. If the <I>expr</I> is a null that represent an\naggregate row this function returns 1, otherwise it returns 0.<P>\n<B>Example:</B>\n<PRE>\nSELECT DECODE(GROUPING(itemid),1,'Total',itemid) item,\n SUM(amount) amount,\n SUM(profit) profit\n FROM saleitems\n GROUP BY ROLLUP (itemid);\n\nITEM AMOUNT PROFIT\n----- ------ ------\n1 12000 2200\n2 2760 195\n3 10000 2200\n4 1300 170\nTotal 26060 4765\n</PRE>
Aggregate Functions:MAX( expr )=Get the largest value of <I>expr</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT MAX(amount) FROM saleitems;\n\nMAX(AMOUNT)\n-----------\n 4000\n</PRE>
Aggregate Functions:MIN ( expr )=Get the smallest value of <I>expr</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT MIN(amount) FROM saleitems;\n\nMIN(AMOUNT)\n-----------\n 240\n</PRE>
Aggregate Functions:REGR_AVGX ( expr1 , expr2 )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nAVG ( expr2 )\n</PRE>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_AVGX ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_AVGX(SYSDATE-SALEDATE,AMOUNT)\n---------- ----------------------------------\n 0 1280\n 8 500\n 10 300\n 13 3333.33333\n 15 1360\n 73 1000\n 74 1400\n</PRE>
Aggregate Functions:REGR_AVGY ( expr1 , expr2 )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nAVG ( expr2 )\n</PRE>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_AVGY ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_AVGY(SYSDATE-SALEDATE,AMOUNT)\n---------- ----------------------------------\n 0 1.0277662\n 8 60.0277662\n 10 71.0277662\n 13 93.3610995\n 15 107.027766\n 73 514.027766\n 74 518.027766\n</PRE>
Aggregate Functions:REGR_COUNT ( expr1 , expr2 )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. This function then returns the number of valid pairs left.<P>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_COUNT ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_COUNT(SYSDATE-SALEDATE,AMOUNT)\n---------- -----------------------------------\n 0 3\n 8 2\n 10 1\n 13 3\n 15 2\n 73 1\n 74 3\n</PRE>
Aggregate Functions:REGR_INTERCEPT ( expr1 , expr2 )=Calculates the y-intercept of the regression line. Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nAVG ( expr1 ) - REGR_SLOPE ( expr1 , expr2 ) * AVG ( expr2 )\n</PRE>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_INTERCEPT ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_INTERCEPT(SYSDATE-SALEDATE,AMOUNT)\n---------- ---------------------------------------\n 0 1.02846065\n 8 60.0284606\n 10\n 13 100.028461\n 15 107.028461\n 73\n 74 518.028461\n</PRE>
Aggregate Functions:REGR_R2 ( expr1 , expr2 )=Represent the determination or goodness of fit for the regression. Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. The value of the functions <CODE>VAR_POP ( expr1 )</CODE> and <CODE>VAR_POP ( expr2 )</CODE> is evaluated after null pairs are removed. The returned value is then one of the following:\n<UL>\n<LI>null if <CODE>VAR_POP ( expr2 )</CODE> = 0.\n<LI>1 if <CODE>VAR_POP ( expr1 )</CODE> = 0 and <CODE>VAR_POP ( expr2 )</CODE> != 0.\n<LI><CODE>POWER ( CORR ( expr1 , expr2 ) , 2 )</CODE> if <CODE>VAR_POP ( expr1 )</CODE> > 0 and <CODE>VAR_POP ( expr2 )</CODE> != 0.\n</UL>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_R2 ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_R2(SYSDATE-SALEDATE,AMOUNT)\n---------- --------------------------------\n 0 1.4215E-39\n 8 2.8571E-38\n 10\n 13 .25\n 15 1\n 73\n 74 0\n</PRE>
Aggregate Functions:REGR_SLOPE ( expr1 , expr2 )=This function returns the slope of the line. Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nCOVAR_POP ( expr1 , expr2 ) / VAR_POP ( expr2 )\n</PRE>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_SLOPE ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_SLOPE(SYSDATE-SALEDATE,AMOUNT)\n---------- -----------------------------------\n 0 -3.554E-42\n 8 0\n 10\n 13 -.002\n 15 0\n 73\n 74 0\n</PRE>
Aggregate Functions:REGR_SXX ( expr1 , expr2 )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nREGR_COUNT ( expr1 , expr2 ) / VAR_POP ( expr2 )\n</PRE>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_SXX ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_SXX(SYSDATE-SALEDATE,AMOUNT)\n---------- ---------------------------------\n 0 4502400\n 8 20000\n 10 0\n 13 666666.667\n 15 819200\n 73 0\n 74 560000\n</PRE>
Aggregate Functions:REGR_SXY ( expr1 , expr2 )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nREGR_COUNT ( expr1 , expr2 ) / VAR_POP ( expr1 )\n</PRE>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_SXY ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_SXY(SYSDATE-SALEDATE,AMOUNT)\n---------- ---------------------------------\n 0 1.3000E-35\n 8 1.0000E-34\n 10 0\n 13 -1333.3333\n 15 0\n 73 0\n 74 0\n</PRE>
Aggregate Functions:REGR_SYY ( expr1 , expr2 )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nREGR_COUNT ( expr1 , expr2 ) / COVAR_POP ( expr1 )\n</PRE>\n<B>Expample:</B><P>\n<PRE>\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_SXY ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_SXY(SYSDATE-SALEDATE,AMOUNT)\n---------- ---------------------------------\n 0 -4.000E-36\n 8 0\n 10 0\n 13 -1333.3333\n 15 0\n 73 0\n 74 0\n</PRE>
Aggregate Functions:STDDEV ( DISTINCT|ALL expr )=Get the standard deviation of <I>expr</I>. This function differs from <CODE>STDDEV_SAMP</CODE> in what it returns when there is only one input row. This function returns 0 and <CODE>STDDEV_SAMP</CODE> returns null. The standard deviation is the square root of the variance defined for the <CODE>VARIANCE</CODE> function.<P>\n<B>Expample:</B>\n<PRE>\nSELECT AVG(amount),STDDEV(amount) FROM saleitems;\n\nAVG(AMOUNT) STDDEV(AMOUNT)\n----------- --------------\n 1628.75 1225.85412\n</PRE>
Aggregate Functions:STDDEV_POP ( expr )=Get the square root of the population variance. This is the same as the square root of the <CODE>VAR_POP</CODE> function.<P>\n<B>Expample:</B>\n<PRE>\nSELECT STDDEV_POP(profit) FROM saleitems;\n\nSTDDEV_POP(PROFIT)\n------------------\n 277.077205\n</PRE>
Aggregate Functions:STDDEV_SAMP ( DISTINCT|ALL expr )=Get the standard deviation of <I>expr</I>. This function differs from <CODE>STDDEV</CODE> in what it returns when there is only one input row. This function returns null and <CODE>STDDEV</CODE> returns 0. The standard deviation is the square root of the variance defined for the <CODE>VARIANCE</CODE> function.<P>\n<B>Expample:</B>\n<PRE>\nSELECT AVG(amount),STDDEV_SAMP(amount) FROM saleitems;\n\nAVG(AMOUNT) STDDEV_SAMP(AMOUNT)\n----------- -------------------\n 1628.75 1225.85412\n</PRE>
Aggregate Functions:SUM ( DISTINCT|ALL expr )=Get the sum of all <I>expr</I> values. If <CODE>DISTINCT</CODE> is specified only distinct values are calculated.<P>\n<B>Expample:</B>\n<PRE>\nSELECT SUM(profit) FROM saleitems;\n\nSUM(PROFIT)\n-----------\n 4765\n</PRE>
Aggregate Functions:VARIANCE ( DISTINCT|ALL expr )=Get the sample variance of the values in <I>expr</I>, if no values are available null is returned. The following calculation is used.\n<PRE>\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n ( COUNT ( expr ) - 1 )\n</PRE>\nIf applied to an empty set 0 is returned.\n<B>Expample:</B>\n<PRE>\nSELECT VARIANCE(profit) FROM saleitems;\n\nVARIANCE(PROFIT)\n----------------\n 81889.8958\n</PRE>
Aggregate Functions:VAR_POP ( expr )=Get the population variance of the values of <I>expr</I>. This is defined by the following formula.<P>\n<PRE>\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n COUNT ( expr )\n</PRE>\n<B>Example:</B>\n<PRE>\nSELECT VAR_POP(profit) FROM saleitems;\n\nVAR_POP(PROFIT)\n---------------\n 76771.7773\n</PRE>
Aggregate Functions:VAR_SAMP ( DISTINCT|ALL expr )=Get the sample variance of the values in <I>expr</I>, if no values are available null is returned. The following calculation is used.\n<PRE>\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n ( COUNT ( expr ) - 1 )\n</PRE>\nIf applied to an empty set null is returned.\n<B>Expample:</B>\n<PRE>\nSELECT VAR_SAMP(profit) FROM saleitems;\n\nVAR_SAMP(PROFIT)\n----------------\n 81889.8958\n</PRE>
Analytic Functions:AVG ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Calculates the avarage value of <I>expr</I>.<P>\nIf <CODE>DISTINCT</CODE> is specified duplicates of the same number is only counted as one\nnumber, if <CODE>ALL</CODE> or nothing is specified all numbers are counted equally. If <CODE>DISTINCT</CODE> is specified you can't use order by and windowing specifications in the <I>analytic_clause</I>.
Analytic Functions:CORR ( expr1 , expr2 ) OVER ( analytic_clause )=Calculate the coefficient of correlation of a set of number pairs.<P>\n\nPairs where either <I>expr1</I> or <I>expr2</I> are eliminated first. Then the following\ncalculation is performed:\n\n<PRE>\nCOVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))\n</PRE>\n\nThe function returns a number, if applied to an empty set NULL is returned.
Analytic Functions:COUNT ( *|DISTINCT|ALL expr ) OVER ( analytic_clause )=Return the number of rows in the resultset. <CODE>DISTINCT</CODE> specifies that only\ndistinct values are to be counted. If <I>expr</I> is NULL it is not counted. <CODE>*</CODE>\n and <CODE>ALL</CODE> indicates that all rows should be counted. If <CODE>DISTINCT</CODE> is specified you can't use order by and windowing specifications in the <I>analytic_clause</I>.
Analytic Functions:COVAR_POP ( expr1 , expr2 ) OVER ( analytic_clause )=Get the population covariance of a set of pairs.<P>\n\nPairs where either <I>expr1</I> or <I>expr2</I> are eliminated first. Then the following\ncalculation is performed:\n\n<PRE>\n(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n\n</PRE>\n\nThe function returns a number, if applied to an empty set NULL is returned.<P>\n
Analytic Functions:COVAR_SAMP ( expr1 , expr2 ) OVER ( analytic_clause )=Get the sample covariance of a set of pairs.<P>\n\nPairs where either <I>expr1</I> or <I>expr2</I> are eliminated first. Then the following\ncalculation is performed:\n\n<PRE>\n(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / (n - 1)\n</PRE>\n\nThe function returns a number, if applied to an empty set NULL is returned.<P>\n
Analytic Functions:CUME_DIST ( ) OVER ( analytic_clause )=This computes the relative position of a specified value in a group of values. For a given row the <CODE>CUME_DIST</CODE> of this row is the number of rows with lower than or equal to the value of the specified row divided by the total number of rows being evaluated. The values returned are greated than 0 and less than or equal to 1.
Analytic Functions:DENSE_RANK () OVER ( analytic_clause )=This function computes the rank of each row returned from a query in comparison to the other rows in the query based on the expression in the <CODE>ORDER BY</CODE> of the <I>analytic_clause</I>. Equal values receive the same rank. The next rank is then added with 1 in comparison to the <CODE>RANK</CODE> who adds the number of tied rows.
Analytic Functions:FIRST_VALUE ( expr ) OVER ( analytic_clause )=Returns the first value of <I>expr</I> in an ordered set of values.
Analytic Functions:LAG ( value_expr , offset , default ) OVER ( analytic_clause )=This function gives access to the previous values returned in the query. If <I>offset</I> is specified it indicates the number of rows before the current row to look, the default is 1. The <I>default</I> parameter can be used to specify the values if outside the specified window, the default is null.
Analytic Functions:LAST_VALUE ( expr ) OVER ( analytic_clause )=Returns the last value of <I>expr</I> in an ordered set of values.
Analytic Functions:LEAD ( expr , offset , default ) OVER ( analytic_clause )=This function gives access to future values returned in the query. If <I>offset</I> is specified it indicates the number of rows after the current row to look, the default is 1. The <I>default</I> parameter can be used to specify the values if outside the specified window, the default is null.
Analytic Functions:MAX ( expr ) OVER ( analytic_clause )=Get the largest value of <I>expr</I>.<P>
Analytic Functions:MIN ( expr ) OVER ( analytic_clause )=Get the smallest value of <I>expr</I>.<P>
Analytic Functions:NTILE ( expr ) OVER ( analytic_clause )=This is function divides a dataset into a number of buckets specified by <I>expr</I> and return the number of the bucket for the current row. The number of rows in each bucket can differ at most 1. If <I>expr</U> is larger than the number of rows only rows number of buckets will be filled with 1. Buckets are numbered from 1 to <I>expr</I>.
Analytic Functions:PERCENT_RANK () OVER ( analytic_clause )=This computes the relative position of a specified value in a group of values similar to the <CODE>CUME_DIST</CODE> function. The difference is that instead of counting the number of rows before or equal divided by the total number of rows in the set you first deduct one from both the number of rows before and the total number of rows.
Analytic Functions:RANK ( ) OVER ( analytic_clause )=This function computes the rank of each row returned from a query in comparison to the other rows in the query based on the expression in the <CODE>ORDER BY</CODE> of the <I>analytic_clause</I>. Equal values receive the same rank. The next rank is then added with the number of rows with this rank value.
Analytic Functions:RATIO_TO_REPORT ( expr ) OVER ( analytic_clause )=This function returns the ratio of <I>expr</I> in comparison to the sum of all <I>expr</I> over the entire set. If <I>expr</I> is null, null is also returned.
Analytic Functions:REGR_AVGX ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nAVG ( expr2 )\n</PRE>\n
Analytic Functions:REGR_AVGY ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nAVG ( expr2 )\n</PRE>\n
Analytic Functions:REGR_COUNT ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. This function then returns the number of valid pairs left.<P>\n
Analytic Functions:REGR_INTERCEPT ( expr1 , expr2 ) OVER ( analytic_clause )=Calculates the y-intercept of the regression line. Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nAVG ( expr1 ) - REGR_SLOPE ( expr1 , expr2 ) * AVG ( expr2 )\n</PRE>\n
Analytic Functions:REGR_R2 ( expr1 , expr2 ) OVER ( analytic_clause )=Represent the determination or goodness of fit for the regression. Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. The value of the functions <CODE>VAR_POP ( expr1 )</CODE> and <CODE>VAR_POP ( expr2 )</CODE> is evaluated after null pairs are removed. The returned value is then one of the following:\n<UL>\n<LI>null if <CODE>VAR_POP ( expr2 )</CODE> = 0.\n<LI>1 if <CODE>VAR_POP ( expr1 )</CODE> = 0 and <CODE>VAR_POP ( expr2 )</CODE> != 0.\n<LI><CODE>POWER ( CORR ( expr1 , expr2 ) , 2 )</CODE> if <CODE>VAR_POP ( expr1 )</CODE> > 0 and <CODE>VAR_POP ( expr2 )</CODE> != 0.\n</UL>\n
Analytic Functions:REGR_SLOPE ( expr1 , expr2 ) OVER ( analytic_clause )=This function returns the slope of the line. Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nCOVAR_POP ( expr1 , expr2 ) / VAR_POP ( expr2 )\n</PRE>\n
Analytic Functions:REGR_SXX ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nREGR_COUNT ( expr1 , expr2 ) / VAR_POP ( expr2 )\n</PRE>\n
Analytic Functions:REGR_SXY ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nREGR_COUNT ( expr1 , expr2 ) / VAR_POP ( expr1 )\n</PRE>\n
Analytic Functions:REGR_SYY ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either <I>expr1</I> or <I>expr2</I> are eliminated to begin with. Then the following function is calculated:\n<PRE>\nREGR_COUNT ( expr1 , expr2 ) / COVAR_POP ( expr1 )\n</PRE>\n
Analytic Functions:ROW_NUMBER ( ) OVER ( analytic_clause )=This function will return a unique value to each row that is returned in the order specify by the <CODE>ORDER BY</CODE> clause of the <I>analytic_clause</I>. The first row returned is 1.
Analytic Functions:STDDEV ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the standard deviation of <I>expr</I>. This function differs from <CODE>STDDEV_SAMP</CODE> in what it returns when there is only one input row. This function returns 0 and <CODE>STDDEV_SAMP</CODE> returns null. The standard deviation is the square root of the variance defined for the <CODE>VARIANCE</CODE> function. If <CODE>DISTINCT</CODE> is specified you can't use order by and windowing specifications in the <I>analytic_clause</I>.\n
Analytic Functions:STDDEV_POP ( expr ) OVER ( analytic_clause )=Get the square root of the population variance. This is the same as the square root of the <CODE>VAR_POP</CODE> function.
Analytic Functions:STDDEV_SAMP ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the standard deviation of <I>expr</I>. This function differs from <CODE>STDDEV</CODE> in what it returns when there is only one input row. This function returns null and <CODE>STDDEV</CODE> returns 0. The standard deviation is the square root of the variance defined for the <CODE>VARIANCE</CODE> function. If <CODE>DISTINCT</CODE> is specified you can't use order by and windowing specifications in the <I>analytic_clause</I>.\n
Analytic Functions:SUM ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the sum of all <I>expr</I> values. If <CODE>DISTINCT</CODE> is specified only distinct values are calculated. If <CODE>DISTINCT</CODE> is specified you can't use order by and windowing specifications in the <I>analytic_clause</I>.
Analytic Functions:VARIANCE ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the sample variance of the values in <I>expr</I>, if no values are available null is returned. The following calculation is used.\n<PRE>\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n ( COUNT ( expr ) - 1 )\n</PRE>\nIf applied to an empty set 0 is returned. If <CODE>DISTINCT</CODE> is specified you can't use order by and windowing specifications in the <I>analytic_clause</I>.
Analytic Functions:VAR_POP ( expr ) OVER ( analytic_clause )=Get the population variance of the values of <I>expr</I>. This is defined by the following formula.<P>\n<PRE>\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n COUNT ( expr )\n</PRE>\n
Analytic Functions:VAR_SAMP ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the sample variance of the values in <I>expr</I>, if no values are available null is returned. The following calculation is used.\n<PRE>\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n ( COUNT ( expr ) - 1 )\n</PRE>\nIf applied to an empty set null is returned. If <CODE>DISTINCT</CODE> is specified you can't use order by and windowing specifications in the <I>analytic_clause</I>.
Char to Char Functions:CHR ( n USING NCHAR_CS )=Get the character specified by the binary equivalent of <I>n</I> in the national character set. If <CODE>USING NCHAR_CS</CODE> is used, this function returns the equivalent of the national <CODE>NVARCHAR2</CODE>. Otherwise the <CODE>VARCHAR2</CODE> character set is user.<P>\n<B>Examples:</B>\n<PRE>\nSELECT CHR ( 65 ) || CHR ( 66 ) || CHR ( 67 ) abc FROM DUAL;\n\nABC\n---\nABC\n</PRE>
Char to Char Functions:CONCAT ( char1 , char2 )=This function concatenates the <I>char1</I> string with <I>char2</I>. Which means it will perform the same task as the operator <CODE>||</CODE>.<P>\n<B>Examples:</B>\n<PRE>\nSELECT CONCAT ( 'Foo' , 'bar' ) FROM DUAL;\n\nCONCAT\n------\nFoobar\n</PRE>
Char to Char Functions:INITCAP ( char )=Return the string of <I>char</I> with the first character in uppercase and all the rest in lowercase.<P>\n<B>Examples:</B>\n<PRE>\nSELECT INITCAP ( 'MaUrItZ' ) FROM DUAL;\n\nINITCAP\n-------\nMauritz\n</PRE>
Char to Char Functions:LOWER ( char )=Returns the string <I>char</I> with all letters in lowercase.<P>\n<B>Examples:</B>\n<PRE>\nSELECT LOWER ( 'FoPPa' ) FROM DUAL;\n\nLOWER\n-----\nfoppa\n</PRE>
Char to Char Functions:LPAD ( char1 , n , char2 )=This function returns <I>char1</I> padded from the left to <I>n</I> number of characters. If <I>char2</I> is specified this is used for padding instead of space which is the default.<P>\n<B>Examples:</B>\n<PRE>\nSELECT LPAD ( '>Padding' , 12 , '-=' ) FROM DUAL;\n\nLPAD('>PADDI\n------------\n-=-=>Padding\n</PRE>
Char to Char Functions:LTRIM ( char1 , char2 )=Removes all characters from the <I>char1</I> available in the set defined by the characters available in the string <I>char2</I> from the left until a character not in the set is encountered.<P>\n<B>Examples:</B>\n<PRE>\nSELECT LTRIM ( '-=-=-=->Text' , '-=>' ) FROM DUAL;\n\nLTRI\n----\nText\n</PRE>
Char to Char Functions:NLSSORT ( char , nlsparam )=This function returns a string used for sorting the <I>char</I>. The value of <I>nlsparam</I> has the form <CODE>'NLS_<I>param</I> = <I>value</I>'</CODE> usually <CODE>'NLS_SORT = <I>sort</I>'</CODE> where sort can either be <CODE>BINARY</CODE> or any other sort specifier.<P>\n<B>Examples:</B>\n<PRE>\nSELECT NLSSORT ( 'ABCÅÄÖ' , 'NLS_SORT = Swedish' ) FROM DUAL;\n\nNLSSORT('ABCÅÄÖ','NLS_SORT=SWEDISH')\n----------------------------------------------------\n14191E898A8B0001010101010100\n</PRE>
Char to Char Functions:NLS_INITCAP ( char , nlsparam )=This function returns the string <I>char</I> with the first character is made uppercase and all the other lowercase. The value of <I>nlsparam</I> has the form <CODE>'NLS_<I>param</I> = <I>value</I>'</CODE> usually <CODE>'NLS_SORT = <I>sort</I>'</CODE> where sort can either be <CODE>BINARY</CODE> or any other sort specifier.<P>\n<B>Examples:</B>\n<PRE>\nSELECT NLS_INITCAP ( 'ABCÅÄÖ' , 'NLS_SORT = Swedish' ) FROM DUAL;\n\nNLS_IN\n------\nAbcåäö\n</PRE>
Char to Char Functions:NLS_LOWER ( char , nlsparam )=This function returns the string <I>char</I> with all letters converted to lowercase. The value of <I>nlsparam</I> has the form <CODE>'NLS_<I>param</I> = <I>value</I>'</CODE> usually <CODE>'NLS_SORT = <I>sort</I>'</CODE> where sort can either be <CODE>BINARY</CODE> or any other sort specifier.<P>\n<B>Examples:</B>\n<PRE>\nSELECT NLS_LOWER ( 'ABCÅÄÖ' , 'NLS_SORT = Swedish' ) FROM DUAL;\n\nNLS_LO\n------\nabcåäö\n</PRE>
Char to Char Functions:NLS_UPPER ( char , nlsparam )=This function returns the string <I>char</I> with all letters converted to uppercase. The value of <I>nlsparam</I> has the form <CODE>'NLS_<I>param</I> = <I>value</I>'</CODE> usually <CODE>'NLS_SORT = <I>sort</I>'</CODE> where sort can either be <CODE>BINARY</CODE> or any other sort specifier.<P>\n<B>Examples:</B>\n<PRE>\nSELECT NLS_UPPER ( 'ABCÅÄÖ' , 'NLS_SORT = Swedish' ) FROM DUAL;\n\nNLS_UP\n------\nABCÅÄÖ\n</PRE>
Char to Char Functions:REPLACE ( char , search , replace )=This function will return a string where every occurance in <I>char</I> of the string <I>search</I> is replaced with the <I>replace</I> string.<P>\n<B>Example:</B>\n<PRE>\nSELECT REPLACE ('Gnu','Gnu','Gnu Is Not Unix') FROM DUAL;\n\nREPLACE('GNU','\n---------------\nGnu Is Not Unix\n</PRE>
Char to Char Functions:RPAD ( char1 , n , char2 )=This function returns <I>char1</I> padded from the right to <I>n</I> number of characters. If <I>char2</I> is specified this is used for padding instead of space which is the default.<P>\n<B>Examples:</B>\n<PRE>\nSELECT RPAD ( 'Padding<' , 12 , '=-' ) FROM DUAL;\n\nRPAD('PADDIN\n------------\nPadding<=-=-\n</PRE>
Char to Char Functions:RTRIM ( char1 , char2 )=Removes all characters from the <I>char1</I> available in the set defined by the characters available in the string <I>char2</I> from the right until a character not in the set is encountered.<P>\n<B>Examples:</B>\n<PRE>\nSELECT RTRIM ( 'Text<=-=-=-' , '<-=' ) FROM DUAL;\n\nRTRI\n----\nText\n</PRE>
Char to Char Functions:SOUNDEX ( char )=Returns a string that contains a phonetic representation of <I>char</I>. This is usefull for comparing strings that sound similarly, but are spelled different. The algorithm used is available in <I>The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth</I>.<P>\n<B>Examples:</B>\n<PRE>\nSELECT 'Johnson sounds like Jonsson'\n FROM DUAL WHERE SOUNDEX ( 'Johnson' ) = SOUNDEX ( 'Jonsson' );\n\n'JOHNSONSOUNDSLIKEJONSSON'\n---------------------------\nJohnson sounds like Jonsson\n</PRE>
Char to Char Functions:SUBSTR ( char , m , n )=This function returns a substring of of the <I>char</I> string. If <I>m</I> is 0 it is treated as 1 which means the beginning of the string, 2 meaning the second character etc. If <I>m</I> is negative it will count from the end of the string with -1 meaning the last character of the string. If <I>n</I> is not specified the rest of the string is returned.<P>\n<B>Examples:</B>\n<PRE>\nSELECT SUBSTR('Henrik P Johnson',8,3) FROM DUAL;\n\nSUB\n---\nP J\n</PRE>
Char to Char Functions:SUBSTRB ( char , m , n )=This function returns a substring of of the <I>char</I> string. The difference from <CODE>SUBSTR</CODE> is that this function counts bytes instead of characters which differs if you use multichar charactersets.<P>\n<B>Examples:</B><P>\nIf run on a UNICODE characterset database.\n<PRE>\nSELECT SUBSTRB('Henrik P Johnson',7,4) FROM DUAL;\n\nSU\n--\nri\n</PRE>
Char to Char Functions:TRANSLATE ( char , from , to )=This function will return a string where every occurance of a character in <I>char</I> is replaced from the character in <I>from</I> with the corresponding character in <I>to</I>. If the character is not available in <I>to</I> it is removed from the result.<P>\n<B>Example:</B>\n<PRE>\nSELECT TRANSLATE ( 'Mauritz' , 'Mauritz' , 'Henrik' ) FROM DUAL;\n\nTRANSL\n------\nHenrik\n</PRE>
Char to Char Functions:TRIM ( LEADING|TRAILING|BOTH trim FROM source )=This function can trim characters from both beginning (If <CODE>LEADING</CODE> or <CODE>BOTH</CODE> is specified) and end (If <CODE>TRAILING</CODE> or <CODE>BOTH</CODE>) with <CODE>BOTH</CODE> being the default. If <I>trim</I> isn't specified any whitespace is the default. Any characyers in the <I>trim</I> set will be removed from the specified ends of <I>source</I>.<P>\n<B>Examples:</B>\n<PRE>\nSELECT TRIM ( '-=-=-=>Text<=-=-=-' , '<-=>' ) FROM DUAL;\n\nTRIM\n----\nText\n</PRE>
Char to Char Functions:UPPER ( char )=Returns the string <I>char</I> with all letters in uppercase.<P>\n<B>Examples:</B>\n<PRE>\nSELECT UPPER ( 'FoPPa' ) FROM DUAL;\n\nUPPER\n-----\nFOPPA\n</PRE>
Char to Number Functions:ASCII ( char )=Get the number representation of the first character in <I>char</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT ASCII('Aloha') FROM DUAL;\n\nASCII('ALOHA')\n--------------\n 65\n</PRE>
Char to Number Functions:INSTR ( string , substring , pos , occurance )=Search for <I>substring</I> in <I>string</I>. If <I>pos</I> is specified it indicates the character in <I>string</I> to start searching, if negative Oracle will search backwards in the string. If <I>occurance</I> is specified it indicates how many hits to discard before returning (1 meaning the first match). The function returns the position of the charater where the match begins in <I>string</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT INSTR('excellence','e',3,2) FROM DUAL;\n\nINSTR('EXCELLENCE','E',3,2)\n---------------------------\n 7\n</PRE>
Char to Number Functions:INSTRB ( str , substring , pos , occurance )=This function is the same as <CODE>INSTR</CODE> except that <I>pos</I> and the returned number is in bytes instead of characters.
Char to Number Functions:LENGTH ( char )=Get the length of <I>char</I> in characters.<P>\n<B>Example:</B>\n<PRE>\nSELECT LENGTH('GlobeCom') FROM DUAL;\n\nLENGTH('GLOBECOM')\n------------------\n 8\n</PRE>
Char to Number Functions:LENGTHB ( char )=Get the length of <I>char</I> in bytes.<P>\n<B>Example:</B><P>\nIf performed on a UNICODE database.\n<PRE>\nSELECT LENGTHB('GlobeCom') FROM DUAL;\n\nLENGTHB('GLOBECOM')\n-------------------\n 16\n</PRE>
Conversion Functions:CHARTOROWID ( char )=Convert a <I>char</I> str string value to a <CODE>ROWID</CODE>. This is the reverse of the <CODE>ROWIDTOCHAR</CODE> function.
Conversion Functions:CONVERT ( char , dest_char_set , source_char_set )=Convert the <I>char</I> string from the <I>source_char_set</I> or the database character set to the <I>dest_char_set</I>. Observe that TOra always communicate with the database in the UTF-8 character set.<P>\n<B>Example:</B>\nAssumes a Latin-1 character set database.\n<PRE>\nSELECT CONVERT ( 'ÅÄÖåäö' , 'UTF8' ) FROM DUAL;\n\nCONVERT('ÅÄÖ\n------------\nÃ\nÃ\n Ãåäö\n</PRE>
Conversion Functions:HEXTORAW ( char )=Convert a string <I>char</I> containing a hexadecimal dump of <CODE>RAW</CODE> data. This is the reverse of the <CODE>RAWTOHEX</CODE> function.
Conversion Functions:NUMTODSINTERVAL ( n , interval )=Converts <I>n</I> to an <CODE>INTERVAL DAY TO SECOND</CODE> literal. The string <I>interval</I> specifies the unit of the value <I>n</I> and can be one of the following <CODE>'DAY'</CODE>, <CODE>'HOUR'</CODE>, <CODE>'MINUTE'</CODE> and <CODE>'SECOND'</CODE>.
Conversion Functions:NUMTOYMINTERVAL ( n , interval )=Converts <I>n</I> to an <CODE>INTERVAL YEAR TO MONTH</CODE> literal. The string <I>interval</I> specifies the unit of the value <I>n</I> and can be one of the following <CODE>'YEAR'</CODE> and <CODE>'MONTH'</CODE>.
Conversion Functions:RAWTOHEX ( char )=This function converts the <CODE>RAW</CODE> value <I>char</I> to a hexadecimal representation of it that can be treated like a string. This is the reverse of the <CODE>HEXTORAW</CODE> function.<P>\n<B>Example:</B>\n<PRE>\nSELECT RAWTOHEX ( 'GlobeCom' ) FROM DUAL;\n\nRAWTOHEX('GLOBEC\n----------------\n476C6F6265436F6D\n</PRE>
Conversion Functions:ROWIDTOCHAR ( rowid )=This function returns a string representation of a <CODE>ROWID</CODE>. Use the <CODE>CHARTOROWID</CODE> to convert the string back to a <CODE>ROWID</CODE>.
Conversion Functions:TO_CHAR( val , fmt , nlsparam )=Convert the <I>val</I> to a string the format specified by the optional <I>fmt</I> parameter and any NLS parameters can be modified by <I>nlsparam</I>. <I>val</I> can be either of date or number. For more information about date and number conversion specifiers refere to the <I>Oracle SQL Reference</I> manual.<P>\n<B>Example:</B>\n<PRE>\nSELECT TO_CHAR ( 123 , '99999.000' ) FROM DUAL;\n\nTO_CHAR(12\n----------\n 123.000\n</PRE>
Conversion Functions:TO_DATE ( char , fmt , nlsparam )=Convert the string <I>char</I> to a date using the format specified by the optional <I>fmt</I> parameter and any NLS parameters can be modified by <I>nlsparam</I>. For more information about date conversion format specifiers refer to the <I>Oracle SQL Reference</I> manual.<P>\n<B>Example:</B>\n<PRE>\nSELECT TO_DATE ( '2000-01-01' , 'YYYY-MM-DD' ) FROM DUAL;\n\nTO_DATE('\n---------\n01-JAN-00\n</PRE>
Conversion Functions:TO_LOB ( long )=This function converts a <I>long</I> value of <CODE>LONG</CODE> or <CODE>LONG RAW</CODE> format to a <CODE>LOB</CODE> object. This can only be used in a <CODE>SELECT</CODE> list of a subquery or in an <CODE>INSERT</CODE> statement.
Conversion Functions:TO_MULTI_BYTE ( char )=This function converts a singlebyte string <I>char</I> to its corresponding multibyte string. If no equivalent multibyte character is available the singlestring character will appear in the returned instead.
Conversion Functions:TO_NUMBER ( char , fmt , nlsparam )=Convert the string <I>char</I> to a number using the format specified by the optional <I>fmt</I> parameter and any NLS parameters can be modified by <I>nlsparam</I>. For more information about number conversion format specifiers refer to the <I>Oracle SQL Reference</I> manual.<P>
Conversion Functions:TO_SINGLE_BYTE ( char )=This function converts a multibyte character string <I>char</I> to its corresponding singlebyte characters. If no equivalent singlebyte character is available the multibyte character will appear in the returned instead.<P>\n
Conversion Functions:TRANSLATE ( text USING CHAR_CS )=Converts <I>text</I> to the character set specified for conversion between the database characterset and the national character set. Specifying <CODE>CHAR_CS</CODE> will return a <CODE>VARCHAR2</CODE> string, <CODE>NCHAR_CS</CODE> will return a <CODE>NVARCHAR2</CODE> string.
Date Functions:ADD_MONTHS ( d , n )=Add <I>n</I> months to the date <I>d</I>. If next resulting month has less days than the day of the month in <I>d</I> the last of that month will be returned, otherwise the result will have the same day of month as <I>d</I>.\n
Date Functions:LAST_DAY ( d )=Get the last of the month of the date <I>d</I>.<P>\n<B>Example:</B><P>\nWill return number of days left in month.\n<PRE>\nSELECT TRUNC ( LAST_DAY ( SYSDATE ) - SYSDATE ) FROM DUAL;\n\nTRUNC(SYSDATE-LAST_DAY(SYSDATE))\n--------------------------------\n 27\n</PRE>
Date Functions:MONTHS_BETWEEN ( d1 , d2 )=Will calculate the number of months between the date <I>d1</I> and <I>d2</I>. If <I>d1</I> is later than <I>d2</I> the result will be positive, otherwise it will be negative. The fraction is calculated based on a 31-day month.<P>\n<B>Example:</B>\n<PRE>\nSELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('1973-12-21','YYYY-MM-DD')) FROM DUAL;\n\nMONTHS_BETWEEN(SYSDATE,TO_DATE('1973-12-21','YYYY-MM-DD'))\n----------------------------------------------------------\n 329.437957\n</PRE>
Date Functions:NEW_TIME ( d , z1 , z2 )=This function converts the date <I>d</I> specified in timezone <I>z1</I> to what the time is in the timezone <I>z2</I>. The timezones can be one of the following <CODE>AST</CODE>, <CODE>ADT</CODE>, <CODE>BST</CODE>,\n<CODE>BDT</CODE>, <CODE>CST</CODE>, <CODE>CDT</CODE>, <CODE>EST</CODE>, <CODE>EDT</CODE>, <CODE>GMT</CODE>, <CODE>HST</CODE>, <CODE>HDT</CODE>, <CODE>MST</CODE>, <CODE>MDT</CODE>, <CODE>NST</CODE>, <CODE>PST</CODE>, <CODE>PDT</CODE>, <CODE>YST</CODE> and <CODE>YDT</CODE>. For more information refer to the <I>Oracle SQL Reference</I> manual.<P>\n<B>Example:</B>\n<PRE>\nSELECT TO_CHAR ( NEW_TIME ( SYSDATE , 'CDT' , 'GMT' ) , \n 'YYYY-MM-DD HH24:MI:SS' )\n FROM DUAL;\n\nTO_CHAR(NEW_TIME(SY\n-------------------\n2001-06-03 18:59:03\n</PRE>
Date Functions:NEXT_DAY ( d , char )=Get the date of the first weekday named by <I>char</I> after the specified date <I>d</I>. <I>char</I> must be in the language of your session and can be either the full name or it's abbreviation.<P>\n<B>Example:</B>\n<PRE>\nSELECT NEXT_DAY ( SYSDATE , 'MONDAY' ) FROM DUAL;\n\nNEXT_DAY(\n---------\n04-JUN-01\n</PRE>
Date Functions:ROUND ( d , fmt )=Round off the date <I>d</I> to the unit specified by the <I>fmt</I> format model. For more information about date formats refer to the <I>Oracle SQL Reference</I> manual. If <I>fmt</I> is not specified the result is rounded to days.<P>\n<B>Example:</B>\n<PRE>\nSELECT ROUND ( SYSDATE , 'MONTH' ) FROM DUAL;\n\nROUND(SYS\n---------\n01-JUN-01\n</PRE>
Date Functions:SYSDATE=Get the current date and time of the database.
Date Functions:TRUNC ( d , char )=Truncate the date <I>d</I> to the unit specified by the <I>fmt</I> format model. For more information about date formats refer to the <I>Oracle SQL Reference</I> manual. If <I>fmt</I> is not specified the result is rounded to days.<P>\n<B>Example:</B>\n<PRE>\nSELECT TRUNC ( SYSDATE , 'MONTH' ) FROM DUAL;\n\nTRUNC(SYS\n---------\n01-JUN-01\n</PRE>
Example Tables=The following SQL script will create the example tables used in these template descriptions.<P>\n<PRE>\n<HR>\nPROMPT CREATE TABLE saleitems\n\nCREATE TABLE saleitems\n(\n saleid NUMBER NOT NULL\n , itemid NUMBER NOT NULL\n , quantity NUMBER \n , amount NUMBER \n , profit NUMBER \n);\n\nPROMPT ALTER TABLE saleitems ADD CONSTRAINT saleitems_pk PRIMARY KEY\n\nALTER TABLE saleitems ADD CONSTRAINT saleitems_pk PRIMARY KEY\n(\n saleid,\n itemid\n);\n\nPROMPT CREATE TABLE sales\n\nCREATE TABLE sales\n(\n saleid NUMBER NOT NULL\n , customerid NUMBER \n , saledate DATE \n , deliverydate DATE \n , sellerid NUMBER \n)\n;\n\nPROMPT ALTER TABLE sales ADD CONSTRAINT sales_pk PRIMARY KEY\n\nALTER TABLE sales ADD CONSTRAINT sales_pk PRIMARY KEY\n(\n saleid\n);\n\nPROMPT CONTENTS OF saleitems\n\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('1','1','1','1000','200');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('1','2','10','1200','60');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('1','3','2','2000','400');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('2','2','2','240','5');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('2','3','3','3000','600');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('2','4','6','600','100');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('3','3','1','1000','200');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('4','2','6','720','30');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('4','1','2','2000','500');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('5','3','4','4000','1000');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('5','1','3','3000','600');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('6','2','5','600','100');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('6','4','4','400','50');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('7','4','3','300','20');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('8','1','3','3000','400');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('9','1','3','3000','500');\nCOMMIT;\n\nPROMPT CONTENTS OF sales\n\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('1','1',TO_DATE('2000-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2000-01-05 00:00:00','YYYY-MM-DD HH24:MI:SS'),'1');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('2','1',TO_DATE('2001-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),NULL,'2');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('3','2',TO_DATE('2000-01-05 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2000-01-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),'3');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('4','2',TO_DATE('2001-02-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-02-24 00:00:00','YYYY-MM-DD HH24:MI:SS'),'2');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('5','3',TO_DATE('2001-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-03-19 00:00:00','YYYY-MM-DD HH24:MI:SS'),'2');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('6','3',TO_DATE('2001-04-03 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-04-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),'3');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('7','2',TO_DATE('2001-03-23 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-04-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),'3');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('8','1',TO_DATE('2001-02-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),'1');\nCOMMIT;\n\n</PRE>
Misc Single-Row Functions:BFILENAME ( ' directory ' , ' filename ' )=
Misc Single-Row Functions:DUMP ( expr , return_fmt , start , length )=Get a <CODE>VARCHAR2</CODE> containing the internal database representation of <I>expr</I>. Data is always always in the database character set. <I>return_fmt</I> can be used to specify the format of the returned data and specifies the radix (Octal, decimal and hexadecimal available) or 17 which specifies single characters. If 1000 is added to the <I>return_fmt</I> will also return the character set information of the data. <I>start</I> and <I>length</I> can be used to extract different portions of the internal data.<P>\n<B>Example:</B>\n<PRE>\nSELECT DUMP ( 'GlobeCom' ) FROM DUAL;\n\nDUMP('GLOBECOM')\n------------------------------------------\nTyp=96 Len=8: 71,108,111,98,101,67,111,109\n\nSELECT DUMP ( 'Mauritz' , 1017 ) FROM DUAL;\n\nDUMP('MAURITZ',1017)\n-----------------------------------------------------\nTyp=96 Len=7 CharacterSet=WE8ISO8859P1: M,a,u,r,i,t,z\n</PRE>
Misc Single-Row Functions:EMPTY_BLOB ( )=Return an empty but initialised BLOB object.<P>
Misc Single-Row Functions:EMPTY_CLOB ( )=Return an empty but initialised BLOB object.<P>
Misc Single-Row Functions:GREATEST ( expr1 , expr2 )=Returns the greatest of the specified expressions.<P>\n<B>Example:</B>\n<PRE>\nSELECT GREATEST ( 1 , 5 , 3 , 8 , -5 ) FROM DUAL;\n\nGREATEST(1,5,3,8,-5)\n--------------------\n 8\n</PRE>
Misc Single-Row Functions:LEAST ( expr1 , expr2 )=Returns the smallest of the specified expressions.<P>\n<B>Example:</B>\n<PRE>\nSELECT LEAST ( 1 , 5 , 3 , 8 , -5 ) FROM DUAL;\n\nLEAST(1,5,3,8,-5)\n-----------------\n -5\n</PRE>
Misc Single-Row Functions:NLS_CHARSET_DECL_LEN ( bytes , csid )=Get the declaration width of an <CODE>NCHAR</CODE> in number of characters. The <I>bytes</I> specifies the width of the column in bytes. The <I>csid</I> indicates the character set ID.<P>\n<B>Example:</B>\n<PRE>\nSELECT NLS_CHARSET_DECL_LEN ( 200 ,\n NLS_CHARSET_ID ( 'ZHT16BIG5FIXED' ) )\n FROM DUAL;\n\nNLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('ZHT16BIG5FIXED'))\n----------------------------------------------------------\n 100\n</PRE>
Misc Single-Row Functions:NLS_CHARSET_ID ( charset )=Get the character set ID from an NLS character set name <I>charset</I>. If <CODE>'NCHAR_CS'</CODE> is specified the multibyte character set of the server and <CODE>'CHAR_CS'</CODE> the singlebyte character set of the server.<P>\n<B>Example:</B>\n<PRE>\nSELECT NLS_CHARSET_ID ( 'ZHT16BIG5FIXED' ) FROM DUAL;\n\nNLS_CHARSET_ID('ZHT16BIG5FIXED')\n--------------------------------\n 1865\n</PRE>
Misc Single-Row Functions:NLS_CHARSET_NAME ( n )=Get the character set name from a character set ID.<P>\n<B>Example:</B>\n<PRE>\nSELECT NLS_CHARSET_NAME ( 1865 ) FROM DUAL;\n\nNLS_CHARSET_NA\n--------------\nZHT16BIG5FIXED\n</PRE>
Misc Single-Row Functions:NVL ( expr1 , expr2 )=This function returns <I>expr1</I> if it isn't null, in that case <I>expr2</I> is returned.<P>\n<B>Example:</B>\n<PRE>\nSELECT NVL ( NULL , 'GlobeCom' ) , NVL ( 'Rules' , 'Sucks' ) FROM DUAL;\n\nNVL(NULL NVL('\n-------- -----\nGlobeCom Rules\n</PRE>
Misc Single-Row Functions:NVL2 ( expr1 , expr2 , expr3 )=This function returns <I>expr2</I> if <I>expr1</I> is not null, and <I>expr3</I> if it is null.<P>\n<B>Example:</B>\n<PRE>\nSELECT NVL2 ( 1 , 'Mauritz' , 'Henrik' ) FROM DUAL;\n\nNVL2(1,\n-------\nMauritz\n</PRE>
Misc Single-Row Functions:SYS_CONTEXT ( namespace , attribute , length )=This function returns the value of <I>attribute</I> associated with the context <I>namespace</I>. The maximum size is by default 256 bytes which can be overridden by specifying the <I>length</I> parameter. For more information see the Oracle documentation.<P>\n<B>Example:</B>\n<PRE>\nSELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') \n FROM DUAL;\n\nSYS_CONTEXT('USERENV','SESSION_USER')\n--------------------------------------------------------------------------------\nSYSTEM\n</PRE>
Misc Single-Row Functions:SYS_GUID ( )=This function returns a generated globally unique number of format <CODE>RAW</CODE> consisting of 16 bytes.<P>\n<B>Example:</B>\n<PRE>\nSELECT SYS_GUID ( ) FROM DUAL;\n\nSYS_GUID()\n--------------------------------\n85DB35952D9A27E7E030007F010040E7\n</PRE>
Misc Single-Row Functions:UID=This function returns an integer that identifies the session user.<P>\n<B>Example:</B>\n<PRE>\nSELECT UID FROM DUAL;\n\n UID\n----------\n 5\n</PRE>
Misc Single-Row Functions:USER=Return the name of the session user.<P>\n<B>Example:</B>\n<PRE>\nSELECT USER FROM DUAL;\n\nUSER\n------------------------------\nSYSTEM\n</PRE>
Misc Single-Row Functions:USERENV ( option )=This function returns information about the current session. For more information about values of <I>option</I> refer to the Oracle documentation.<P>\n<B>Example:</B>\n<PRE>\nSELECT USERENV ( 'SESSIONID' ) FROM DUAL;\n\nUSERENV('SESSIONID')\n--------------------\n 13809\n</PRE>
Misc Single-Row Functions:VSIZE ( expr )=Get the number of bytes needed to store the value of <I>expr</I> in the internal database representation.<P>\n<B>Example:</B>\n<PRE>\nSELECT VSIZE ( 'Mauritz' ) FROM DUAL;\n\nVSIZE('MAURITZ')\n----------------\n 7\n</PRE>
Number functions:ABS ( n )=ABS returns the absolute value of <I>n</I>.<P>\n<B>Example:</B><P>\n<PRE>\nSELECT ABS(-32) FROM DUAL;\n\nABS(-32)\n--------\n 32\n</PRE>
Number functions:ACOS ( n )=Get the arc cosine of <I>n</I>. The angle unit is radeans.<P>\n<B>Example:</B>\n<PRE>\nSELECT ACOS ( 0.5 ) FROM DUAL;\n\n ACOS(0.5)\n----------\n1.04719755\n</PRE>
Number functions:ADD_MONTHS ( d , n )=Add <I>n</I> months to the date <I>d</I>. If next resulting month has less days than the day of the month in <I>d</I> the last of that month will be returned, otherwise the result will have the same day of month as <I>d</I>.\n
Number functions:ASIN ( n )=Get the arc sine of <I>n</I>. The angle unit is radeans.<P>\n<B>Example:</B>\n<PRE>\nSELECT ASIN ( 0.5 ) FROM DUAL;\n\n ASIN(0.5)\n----------\n.523598776\n</PRE>
Number functions:ATAN ( n )=Get the arc tangentent of <I>n</I>. The angle unit is radeans.<P>\n<B>Example:</B>\n<PRE>\nSELECT ATAN ( 0.5 ) FROM DUAL;\n\n ATAN(0.5)\n----------\n.463647609\n</PRE>
Number functions:ATAN2 ( n , m )=Get the arc tangentent of <I>n</I> / <I>m</I>. The angle unit is radeans.<P>\n<B>Example:</B>\n<PRE>\nSELECT ATAN2 ( 1 , 2 ) FROM DUAL;\n\nATAN2(1,2)\n----------\n.463647609\n</PRE>
Number functions:BITAND ( argument1 , argument2 )=Calculate the bitwise and of <I>argument1</I> and <I>argument2</I> which must both be positive integers.
Number functions:CEIL ( n )=Return the smallest integer greater than or equal to <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT CEIL ( 1.3 ) FROM DUAL;\n\n CEIL(1.3)\n----------\n 2\n</PRE>
Number functions:COS ( n )=Return the cosine of <I>n</I>. The angle unit is radeans.<P>\n<B>Example:</B>\n<PRE>\nSELECT COS ( ACOS ( 0.5 ) ) FROM DUAL;\n\nCOS(ACOS(0.5))\n--------------\n .5\n</PRE>
Number functions:COSH ( n )=Return the hyperbolic cosine of <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT COSH ( 1 ) FROM DUAL;\n\n COSH(1)\n----------\n1.54308063\n</PRE>
Number functions:EXP ( n )=Calculate e raised to the <I>n</I>:th power.<P>\n<B>Example:</B>\n<PRE>\nSELECT EXP( 1 ) FROM DUAL;\n\n EXP(1)\n----------\n2.71828183\n</PRE>
Number functions:FLOOR ( n )=Return the largest integer smaller than or equal to <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT FLOOR ( 1.3 ) FROM DUAL;\n\nFLOOR(1.3)\n----------\n 1\n</PRE>
Number functions:LN ( n )=Calculate the natural logaritm of <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT LN ( EXP ( 1 ) ) FROM DUAL;\n\nLN(EXP(1))\n----------\n 1\n</PRE>
Number functions:LOG ( m , n )=Calculate the logarithm with base <I>m</I> of <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT LOG ( 10 , 100 ) FROM DUAL;\n\nLOG(10,100)\n-----------\n 2\n</PRE>
Number functions:MOD ( m , n )=Calculate the remainder of <I>m</I> divided by <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT MOD ( 15 , 4 ) , MOD ( -14 , 4 ) FROM DUAL;\n\n MOD(15,4) MOD(-14,4)\n---------- ----------\n 3 -2\n</PRE>
Number functions:POWER ( m , n )=Calculate <I>m</I> raised to the power of <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT POWER(4,0.5) FROM DUAL;\n\nPOWER(4,0.5)\n------------\n 2\n</PRE>
Number functions:ROUND ( n , m )=Rounds the number <I>n</I> off to <I>m</I> places right of the decimal. If <I>m</I> is not specified <I>n</I> is rounded to an integer.<P>\n<B>Example:</B>\n<PRE>\nSELECT ROUND ( 123 , -1 ) FROM DUAL;\n\nROUND(123,-1)\n-------------\n 120\n</PRE>
Number functions:SIGN ( n )=Return the sign of <I>n</I>. This means -1 is returned if <I>n</I> is below 0, 0 if it is equal to 0 and 1 if above.<P>\n<B>Example:</B>\n<PRE>\nSELECT SIGN(12) FROM DUAL;\n\n SIGN(12)\n----------\n 1\n</PRE>
Number functions:SIN ( n )=Return the sine of <I>n</I>. The angle unit is radeans.<P>\n<B>Example:</B>\n<PRE>\nSELECT SIN ( ASIN ( 0.5 ) ) FROM DUAL;\n\nSIN(ASIN(0.5))\n--------------\n .5\n</PRE>
Number functions:SINH ( n )=Return the hyperbolic sine of <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT SINH ( 1 ) FROM DUAL;\n\n SINH(1)\n----------\n1.17520119\n</PRE>
Number functions:SQRT ( n )=Calculate the square root of <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT SQRT(100) FROM DUAL;\n\n SQRT(100)\n----------\n 10\n</PRE>
Number functions:TAN ( n )=Return the tangent of <I>n</I>. The angle unit is radeans.<P>\n<B>Example:</B>\n<PRE>\nSELECT TAN ( ATAN ( 0.5 ) ) FROM DUAL;\n\nTAN(ATAN(0.5))\n--------------\n .5\n</PRE>
Number functions:TANH ( n )=Return the hyperbolic tangent of <I>n</I>.<P>\n<B>Example:</B>\n<PRE>\nSELECT TANH ( 1 ) FROM DUAL;\n\n TANH(1)\n----------\n.761594156\n</PRE>
Number functions:TRUNC ( n , m )=Rounds the number <I>n</I> off to <I>m</I> places right of the decimal downwards. If <I>m</I> is not specified <I>n</I> is rounded to an integer.<P>\n<B>Example:</B>\n<PRE>\nSELECT TRUNC ( 123 , -1 ) FROM DUAL;\n\nTRUNC(123,-1)\n-------------\n 120
Object Functions:DEREF ( expr )=Returns an object reference to the object of <I>expr</I>.
Object Functions:MAKE_REF ( table|view , key )=Create a referens to a row of view or table.
Object Functions:REF ( correlation )=See <I>Oracle SQL Reference</I> for more information about this function.
Object Functions:REFTOHEX ( expr )=Convert a reference <I>expr</I> to a hexadecimal value.
Object Functions:VALUE ( correlation )=See <I>Oracle SQL Reference</I> for more information about this function.
|