How do I take user inputs in CASE statements in SQL?

Question

SELECT 
    &input,
    (CASE input
         WHEN '1' THEN 'Yes'
         WHEN '2' THEN 'No'
         WHEN '3' THEN 'Maybe'
         ELSE 'Wrong'
     END) Answer
FROM DUAL;

This is the query I'm trying to execute in SQL Developer, but I get an error:

Error at Command Line : 2 Column : 7
Error report -
SQL Error: ORA-00904: "INPUT": invalid identifier
00904. 00000 - "%s: invalid identifier"

Can anyone help me out?

Answer 1

As Alex commented, if you want to use that substitution variable twice, you'd rather precede its name with double ampersands. It would work with one as well, but then Oracle would prompt you twice and you'll have to be careful not to enter different values. On the other hand, && will prevent that, but also require you to undefine the variable if you want to run the same code again, providing a different value.

SQL> select &input,
  2         case &input when 1 then 'Yes'
  3                     when 2 then 'No'
  4                     when 3 then 'Maybe'
  5         end as answer
  6  from dual;
Enter value for input: 1       --> this is for line #1
Enter value for input: 3       --> line #2; note that I provided different values

         1 ANSWE
---------- -----
         1 Maybe

SQL> /
Enter value for input: 2   --> reran, this time providing same values
Enter value for input: 2

         2 AN
---------- --
         2 No

SQL>

---

With double ampersands:

SQL> select &&input,
  2         case &&input when 1 then 'Yes'
  3                      when 2 then 'No'
  4                      when 3 then 'Maybe'
  5         end as answer
  6  from dual;
Enter value for input: 1    --> see? I have to provide only one value

         1 ANS
---------- ---
         1 Yes

SQL> /                      --> but, if I rerun the same code, it uses previous value ...

         1 ANS
---------- ---
         1 Yes

SQL> undefine input         --> ... so you have to undefine it first
SQL> /
Enter value for input: 3

         3 ANSWE
---------- -----
         3 Maybe

SQL>

---

Or, you could use a CTE, provide value once and reference it later:

SQL> with temp as
  2    (select &input as val from dual)
  3  select val,
  4         case val when 1 then 'Yes'
  5                  when 2 then 'No'
  6                  when 3 then 'Maybe'
  7         end as answer
  8  from temp;
Enter value for input: 2

       VAL AN
---------- --
         2 No

SQL> /
Enter value for input: 1

       VAL ANS
---------- ---
         1 Yes

SQL>