rspec/rules/S2460/plsql/rule.adoc

== Why is this an issue?

``++DECODE++`` is an old function that has been replaced by the easier to understand and more common ``++CASE++``. Unlike ``++DECODE++``, ``++CASE++`` may also be used directly within PL/SQL.


=== Noncompliant code example

[source,sql]
----
SET SERVEROUTPUT ON

DECLARE
  operand CHAR(1) := 'B';
  l_result PLS_INTEGER;
BEGIN
  -- Noncompliant
  SELECT DECODE(operand, 'A', 1
                       , 'B', 2
                       , 'C', 3
                       , 'D', 4
                       , 'E', 5
                       , 'F', 6
                       , 7)
  INTO l_result
  FROM dual;

  DBMS_OUTPUT.PUT_LINE('l_result = ' || l_result); -- 2
END;
/
----


=== Compliant solution

[source,sql]
----
SET SERVEROUTPUT ON

DECLARE
  operand CHAR(1) := 'B';
  l_result PLS_INTEGER;
BEGIN

  l_result := CASE operand
                WHEN 'A' THEN 1
                WHEN 'B' THEN 2
                WHEN 'C' THEN 3
                WHEN 'D' THEN 4
                WHEN 'E' THEN 5
                WHEN 'F' THEN 6
                ELSE 7
              END;

  DBMS_OUTPUT.PUT_LINE('l_result = ' || l_result); -- 2
END;
/
----


=== Exceptions

No issue is raised when ``++DECODE++`` contains only one case (i.e. only one ``++search++`` and one ``++result++`` components) because using ``++CASE++`` would make the code less readable in this scenario.

ifdef::env-github,rspecator-view[]

'''
== Implementation Specification
(visible only on this page)

=== Message

Replace this use of "DECODE" with "CASE".


endif::env-github,rspecator-view[]