Decode function in Oracle pl/sql


DECODE function in Oracle pl/sql





As part of Oracle pl sql training, we will see this DECODE function in Oracle plsql.

a> This DECODE function is available in Oracle plsql.
b> This function is not available in MySQL and SQl server.

DECODE syntax:

SELECT DECODE ("Column_name", "search_value_1","result_1",
                                                               "search_value_2","result_2",
                                                               "search_value_3","result_3"
                                                                              "
                                                                              "
                                                                              --
                                                                              --
                                                               "[search_value_n","result_n]" Result column name from Table_Name.

Sample table

 Table Name: Fruit_Details


To display the short name for the Fruits_Name, we are categorize like as below.                                 

 App for Apple                                                                                                                                         Ora  for Orange                                                                                                                                      
 Guv for Guvava                                                                                                                                      
Che for Cherry                                                                                                                                      

SELECT DECODE (Fruits_Name, 'Apple', 'App',
                                                               'Orange', 'Ora', 
                                                                'Guvava', 'Guv', 
                                                                                                   'Cherry', 'Che',                                                                                            'Others') Fruit, Cost, Sale_Date
FROM Fruit_Details; 

Result:

To achieve the same result as above, we need to use CASE function in SQL server and MySQL.


Track us for more functions in Oracle plsql !!!




Comments

Post a Comment

Popular posts from this blog

SUBSTR function in Oracle pl/sql 

Image
SUBSTR function in Oracle Pl Sql In real time projects the requirement might come like to extract the length of data from the particular data. In this blog, we will see how we can execute this requirement through Oracle pl sql by using Oracle/PLSQL SUBSTR function.   Syntax of SUBSTR function in Oracle PL SQL is: SUBSTR( string, starting_position [, Total length of the data ] ) Parameters of SUBSTR string Input data which we are going to use for extraction process. starting position It denotes the starting position of length of the data. The position of the string will always start from 1. length Optional. It denotes the length of the character to extract. If this parameter is not given, then the SUBSTR function will return the entire string from the starting position which we given in syntax. The SUBSTR function is used to returns a string value. If length of the data is a negative number, then the SUBSTR function will return a NULL value. Rule of...
Read more

StoredprocedureinOracleplsql

​                                                 Learn Stored procedure concept in Oracle plsql Here in this blog just we are going to see stored procedure concept in Oracle plsql. We will include further topics in Oracle plsql training in separate blogs. 1. Stored procedure is also called as PL/SQL block 2.   Stored procedure receive input in the form of parameters and performs some task and it may or may not returns a value. 3.   Procedures are created to perform one or more DML operations over database.     (Ex: To create one or more DML operation (Insert or Delete or update) over database, we are creating        Stored procedure) SYNTAX for Stored procedure: CR...
Read more