You can view EDUCBA’s recommended articles for more information. If default is omitted, then Oracle returns null. If no match is found, then Oracle returns default. If expr is equal to a search, then Oracle Database returns the corresponding result. We hope that this EDUCBA information on “SQL DECODE()” was beneficial to you. Database SQL Language Reference DECODE Syntax Description of the illustration ''decode.gif'' Purpose DECODE compares expr to each search value one by one. It is a built-in function in ORACLE / PL SQL database management servers. The function is a close relative of CASE statements. In the above example, we have performed the following task using the DECODE function and have then ordered the result set by college_id.ĭECODE function is used to perform procedural IF-THEN-ELSE logic in SQL. SQL query to categories college fees into affordable and expensive for an Indian student, considering everything above $ 10000 as expensive.ĭECODE(fees,10000,'Affordable','Expensive') The Oracle decode statement can be used in PL/SQL and it was developed to allow us to transform data values at retrieval time. In the above example, we have performed the following IF-THEN-ELSE logic statements and then ordered the entire result set by college_id.Ĭode: IF college_name = 'Massachusetts Institute of Technology'ĮLSE IF college_name = 'California Institute of Technology' The data in the “college_details” table after performing the above mentioned INSERT operations looks something as shown below: INSERT INTO college_details VALUES (10003, 'Massachusetts Institute of Technology', 'Massachusetts,India', 51520) select * from college_details Create a function in SQL Server as below and replace the DECODE with dbo. In Oracle, you can use DECODE function to evaluate a list of expressions, and if a value is matched return the corresponding result. INSERT INTO college_details VALUES (10004, 'California Institute of Technology', 'California ,USA', 60520) INSERT INTO college_details VALUES (10002, 'Indian Institute of Technology Bombay', 'Mumbai,India', 10000) We can use the following insert statements.Ĭode: INSERT INTO college_details VALUES (10001, 'Indian Institute of Technology Roorkee', 'Roorkee,India', 10000) Having created the table, let us now input some random data in it to work with in the subsequent exercises. We can use the following SQL CREATE TABLE statement to perform the task.Ĭollege_name character varying(255) NOT NULL,Ĭollege_location character varying(255) NOT NULL, Let us first create a ‘college_details’ table which contains college id, college name, location and fees for demonstration purposes. The simple illustration of the above mentioned decode function is as follows: The functionality of DECODE in ORACLE with following flowchart. And it finally converts back the data_type of result to the data_type of the expression. The DECODE function automatically converts or casts the expression to the data type of the first search argument or search_1. If it’s FALSE then DEFAULT value is returned. SQL Code Formatter uses server side logic to beautify SQL statements and SQL queries.The first step is comparison of expression and search_1, if the expression = search_1 is TRUE then result_1 is returned.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |