Introduction to the Decode () function in SQL

Syntax for the decode () function:

1 Select decode (ColumnName, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)23 from talbename45Where ...

Where: ColumnName is the column defined in the table to be selected;

The default value can be the column name you want to choose, or the other values you want to define, such as other;

Primary role: Equivalent to the IF statement, the query results are translated into other values. (i.e. in other forms).

To illustrate:

Now defines a table named output, where two column definitions are Monthid (VAR) and sale (number type), translated to d,=2000 when translated to c,=3000 when sale value =1000 translation to a , if other values are translated to other:

 select  Monthid, decode (Sale,1000 ,  " d   ", 2000 , "  c   ", 3000 , "  b   ", 4000 ,  " a   ", ' other ') sale from  output  

To compare to only one value:

Select NULL,'---', sale) sale from output

Other functions, such as the NVL () function or the sign () function, can be used in decode:

NVL (EXPR1,EXPR2)

If EXPR1 is null, the EXPR2 is returned, otherwise EXPR1 is returned.

SELECT NAME,NVL (To_char (COMM),'notapplication' from TABLE1;

If you use the Decode function:

Select monthid,decode (NVL (sale,6000),6000,'NG', ' OK '  from output;

The sign () function returns 0, 1,-1, depending on whether a value is 0, positive, or negative.

Use the following SQL statement to take a smaller value:

Select monthid,decode (sign (Sale-6000),1, sale,6000   from output;

Introduction to the Decode () function in SQL