|
Nonstandard
SQL..."check local listings"
-
INTERSECT and MINUS are like the UNION statement, except that INTERSECT
produces rows that appear in both queries, and MINUS produces rows that
result from the first query, but not the second.
-
Report Generation Features: the COMPUTE clause is placed at the end of a
query to place the result of an aggregate function at the end of a listing,
like COMPUTE SUM (PRICE); Another option is to use break logic:
define a break to divide the query results into groups based on a column,
like BREAK ON BUYERID. Then, to produce a result after the listing
of a group, use COMPUTE SUM OF PRICE ON BUYERID. If, for example,
you used all three of these clauses (BREAK first, COMPUTE on break second,
COMPUTE overall sum third), you would get a report that grouped items by
their BuyerID, listing the sum of Prices after each group of a BuyerID's
items, then, after all groups are listed, the sum of all Prices is listed,
all with SQL-generated headers and lines.
-
In addition to the above listed aggregate functions, some DBMS's allow
more functions to be used in Select lists, except that these functions (some
character functions allow multiple-row results) are to be used with an
individual value (not groups), on single-row queries. The functions
are to be used only on appropriate data types, also. Here are some Mathematical
Functions:
|
ABS(X)
|
Absolute value-converts negative numbers to positive,
or leaves positive numbers alone
|
|
CEIL(X)
|
X is a decimal value that will be rounded up.
|
|
FLOOR(X)
|
X is a decimal value that will be rounded down.
|
|
GREATEST(X,Y)
|
Returns the largest of the two values.
|
|
LEAST(X,Y)
|
Returns the smallest of the two values.
|
|
MOD(X,Y)
|
Returns the remainder of X / Y.
|
|
POWER(X,Y)
|
Returns X to the power of Y.
|
|
ROUND(X,Y)
|
Rounds X to Y decimal places. If Y is omitted, X is
rounded to the nearest integer.
|
|
SIGN(X)
|
Returns a minus if X < 0, else a plus.
|
|
SQRT(X)
|
Returns the square root of X.
|
Character Functions
|
LEFT(<string>,X)
|
Returns the leftmost X characters of the string.
|
|
RIGHT(<string>,X)
|
Returns the rightmost X characters of the string.
|
|
UPPER(<string>)
|
Converts the string to all uppercase letters.
|
|
LOWER(<string>)
|
Converts the string to all lowercase letters.
|
|
INITCAP(<string>)
|
Converts the string to initial caps.
|
|
LENGTH(<string>)
|
Returns the number of characters in the string.
|
|
<string>||<string>
|
Combines the two strings of text into one, concatenated
string, where the first string is immediately followed by the second.
|
|
LPAD(<string>,X,'*')
|
Pads the string on the left with the * (or whatever
character is inside the quotes), to make the string X characters long.
|
|
RPAD(<string>,X,'*')
|
Pads the string on the right with the * (or whatever
character is inside the quotes), to make the string X characters long.
|
|
SUBSTR(<string>,X,Y)
|
Extracts Y letters from the string beginning at
position X.
|
|
NVL(<column>,<value>)
|
The Null value function will substitute <value> for any NULLs for in the <column> If the current value of <column> is not NULL, NVL has no effect.
|
|
