‘None of the above’ is our default value, in case our expression is not equal to any search value. In this syntax, the COALESCE () function returns the first non-null expression in the list. The DECODE function compares our expression with all the search values and returns the result corresponding to that search value. In this query, our expression is 1 + 1 and 1, 2, 3 are search values while ‘One’, ‘Two’, ‘Three’ are results corresponding to each search value. Lets understand how a DUAL function works with the help of a simple example: Examples 1.DECODE Function with DUAL Table If default value is omitted from the query, the DECODE function returns NULL. If expression does not match any of the search value, then the query returns the default value. Or you can use the reverse operation of pivot UNPIVOTto break up the columns to become rows, as is possible in Oracle Database 11g. Of course, you can write a complex SQL:Loader or SQL script using DECODE to load the data into CUSTOMERS table. The data type of the returned value is the data type of the first result value that is returned. The spreadsheet data must be de-normalized to a relational format and then stored. If we don’t provide default value then query returns NULL. If expression is equal to search1 is returned, if it equal to search2 then result2 is returned and so on.ĭefault – If expression is not equal to any search value, then the query returns the default value. Result – If expression value is equal to the search value, then the DECODE function returns result.

The data types of all subsequent search values are converted to the data type of first search value. Search – These are the values against which expression is compared. The data type of this value is converted to the data type of the first search value before comparing. Expression – This is the value that we provide to compare with other search values.

Oracle decode