When the same can be done more cleanly with CASE, go for CASE. In very simple situations, DECODE is shorter and easier to understand than CASE.Ĭomplicated logical comparisons in DECODE, even if technically achievable, are a recipe for messy, bug-prone code. CASE is ANSI SQL-compliantĬASE complies with ANSI SQL. ORA-00932: inconsistent datatypes: expected NUMBER got CHAR 6. CASE expects datatype consistency, DECODE does notĬompare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error. The “ searched CASE” works as does DECODE.ĥ. Careful! CASE handles NULL differentlyĬheck out the different results with DECODE vs NULL. SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end) Ĥ. Find out how to use the DECODE function in the WHERE clause, LIKE, greater than, and UPDATE statements. See the syntax, parameters, examples, and differences with CASE statement. PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL Learn how to use the Oracle DECODE function, a useful function for comparing values and performing IF-THEN-ELSE logic. SQL> create or replace procedure proc_test (i number) David, > How does one do the equivalence of Oracles DECODE in PostgreSQL > select > decode (v.media, V, VHS, L, Laser Disk. This can be done elegantly with CASE.ģ when sal =1000 and sal = 2000 and sal select e.ename,Ĥ - set the category based on ename listĥ when e.ename in ('KING','SMITH','WARD')Ħ when 'a' then dbms_output.put_line('excellent') ħ when 'b' then dbms_output.put_line('very good') Ĩ when 'c' then dbms_output.put_line('good') ĩ when 'd' then dbms_output.put_line('fair') ġ0 when 'f' then dbms_output.put_line('poor') ġ1 else dbms_output.put_line('no such grade') ĬASE can even work as a parameter to a procedure call, while DECODE cannot. It takes some complex coding – forcing ranges of data into discrete form – to achieve the same effect with DECODE.Īn example of putting employees in grade brackets based on their salaries. CASE is capable of other logical comparisons such as etc. Decode SQL Decode Function in SQL - DECODE is a function in Oracle and is also used to provide if-then-else type of logic to SQL. CASE can work with logical operators other than ‘=’ĭECODE performs an equality check only. We’ll go through detailed examples in this article.ġ. There is a lot else CASE can do though, which DECODE cannot. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.Įverything DECODE can do, CASE can. Databases before Oracle 8.1.6 had only the DECODE function.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |