Summary of common functions and expressions of Orale report

The recent period of time to do dozens of reports, through the native SQL analysis of the data, but also a certain understanding, found some of the functions and expressions used more frequently, is summarized as follows:

(1),round () function

The round function is the result of rounding a number by the specified number of decimal digits

Grammatical form: ROUND (number, decimal_places)

Number: The value to be processed

Decimal_places: Rounding, decimal number (preset to 0)

Example:

Select round (123.456, 0) from dual; Backhaul 123 Select Round (123.456, 1) from dual; Backhaul 123.5 Select Round (123.456, 2) from dual; Backhaul 123.46 Select Round (123.456, 3) from dual; Backhaul 123.456 Select Round ( -123.456, 2) from dual; Backhaul-123.46

　　(2), To_char () function

Syntax form: to_char (date, ' Format model ')

To_char (numeric, ' Format model ')

The To_char () function is basically the most common function we use, converting a date or number to a string and not specifying a string length.

In doing the report more is to use TO_CHAR () to the number of specific formatting, and then round function, we want to retain a number of valid two digits, we can combine round,

1 to_char (ROUND-/2                                                         *,  2  'FM999990.00')

Then To_char () also has a lot of formatted statements to continue learning.

　　　(3), decode () function

This function is currently only available from Oracle PL/SQL, which is very powerful

Syntax form: decode (condition, value 1, translation value 1, value 2, translation value 2, value n, translation value N, ...) Default value),

IF condition = value 1 Then
RETURN (translation value 1)
elsif condition = value 2 Then
RETURN (translation value 2)
......
elsif condition = value n Then
RETURN (translated value N)
ELSE
RETURN (default value)
END IF

Of course, another common function of the Decode function is the comparison size,

Select decode (sign (variable 1-variable 2), 1, variable 1, variable 2) from dual; --Take a smaller value
The sign () function returns 0, 1, 1, depending on whether a value is 0, positive, or negative.

eg

Variable 1=10, variable 2=20

SIGN (variable 1-variable 2) returns the -1,decode decoding result as "Variable 1", which achieves the purpose of taking a smaller value. This function in the report analysis, the use is not too much.

There are many other functions that use high frequency, such as SUM (), count (), NVL (), and so on, I will continue to summarize!!!

　　　　　

Orale a summary of common functions and expressions for reports