2/12/2024 0 Comments Decode oracle sql![]() SELECT college_id, DECODE(college_name,'Massachusetts Institute of Technology', SQL query to illustrate abbreviation of college names based on the available data using DECODE function. Simple illustration of above mentioned DECODE function is as follows: In this example, we have performed a simple SQL task for categorizing colleges based on their location. Simple SQL query to illustrate use of DECODE function.ĭECODE (college_id, 10003,'Massachusetts, USA', 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 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. The first step is comparison of expression and search_1, if the expression = search_1 is TRUE then result_1 is returned. We can use the DECODE function as a part of the SELECT statement, ORDER BY etc. It is more or less like the ELSE statement in IF-THEN-ELSE.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |