Oracle Judgment Statement Collection (reprint)

SELECT Decode (sign (to_date (' 2008-05-01 ', ' yyyy-mm-dd ')-
To_date (' 2008-03-01 ', ' yyyy-mm-dd ')),
1,
To_date (' 2008-05-02 ', ' yyyy-mm-dd '),
To_date (' 2008-03-02 ', ' yyyy-mm-dd '))
From dual

--decode only supports comparison values for equality and inequality, so sign (positive returns 1, otherwise 0) and 1.

Decode (Floor (A/b), 0,a,b)
Simple, if a <b so floor (A/b) =0
Use decode to judge if =0 returns a, otherwise, returns B

With Case...when

Select Case
when
To_date (' 2008-05-01 ', ' Yyyy-mm-dd ') >=to_date (' 2008-03-01 ', ' yyyy-mm-dd ')
Then
To_date (' 2008-05-01 ', ' yyyy-mm-dd ')
else
To_date (' 2008-03-01 ', ' yyyy-mm-dd ')
End
From dual

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Decode usage:

For example, select COUNT (*) from News&NBSP;

We count the number of news articles, if more than 1000, the hint is greater than the limit. . &NBSP;

such as Select Decode (count (*), 1000, ' exceeded limit ', limit) &NBSP;

such that the limit output is the ' exceeded limits ' string.
We can also use this to determine the size &NBSP;

a=10, variable B=20&NBSP;

The sign (A-B) returns the -1,decode decoding result as" Variable 1 ", which achieves the purpose of taking a smaller value. &NBSP;

The sign () function returns 0, 1, 1, depending on whether a value is 0, positive, or negative &NBSP;

Decode (sign (A-B), -1,a,b)
If a<b, the value of output a &NBSP;
-----------------------------------------------------

Decode function Description:
The Decode function can compare an input value to a parameter list in a function and return a corresponding value based on the input value.
The parameter list of a function is a number of sequential pairs consisting of several numeric values and their corresponding result values. Of course, the function also has a default return value if it fails to match any one of the actual argument order pairs successfully. Unlike other functions in SQL, the Decode function can also recognize and manipulate null values.

If you don't have a code table in your app, a key–>value conversion using the Docode function is a good alternative, just a hassle for later maintenance of your application.

The syntax is as follows:
DECODE (Control_value,value1,result1[,value2,result2 ...] [, Default_result]);

Parameter description:
Control _value
The value to try to process. Can be a column of a table in a database, the Decode function compares the value to a sequence of subsequent pairs to determine the return value.

Value1
is the numeric value of a constituent order pair. If the input value matches successfully, the corresponding result is returned. corresponding to an empty return value, you can use the keyword NULL for the corresponding

Result1
Is the result value of a constituent sequence pair.

Default_result
The default value returned by the function when it fails to match any one of the values.

Example:
First create a sale table and insert some test data:

CREATE TABLE class (ID char (2), name Varchar2 (), Valid_flag char (1));
INSERT into class values (' 01 ', ' Language ', ' Y ');
INSERT into class values (' 02 ', ' Math ', ' Y ');
INSERT into class values (' 03 ', ' Foreign language ', ' N ');
INSERT into class values (' 04 ', ' history ');

First look at the basic usage of the DECODE function:

Select ID, name, decode (Valid_flag, ' Y ', ' valid ', ' N ', ' invalid ', ' not set ') from class;

ID NAME DECODE (valid_flag, ' Y ', ' effective ', '
-- -------------------------------------------------- ------------------------------
01 Language Effective
02 Math Effective
03 Invalid foreign language
04 History Not set

Description
When Valid_flag is ' Y ', it returns ' valid ';
Returns ' Invalid ' when month is ' N ';
The other returns ' not set '.

The above SQL statements can be implemented by case:

Select ID, name, case Valid_flag
When ' Y ' then ' effective '
When ' N ' then ' invalid '
Else ' not set ' end
from class;

As can be seen from the SQL statements above, using the Decode function is much more concise.

In addition, you can also use decode in order by:

Example: Table Table_subject, with subject_name columns. It is required to sort by: language, number, and order. At this point, it is very easy to use the decode to complete the requirements.

SELECT * from Table_subject ORDER by decode (subject_name, ' language ', 1, ' Math ', 2,, ' Foreign Language ', 3)

Oracle Judgment Statement Collection (reprint)