Skip to content
Advertisement

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

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?

Advertisement

Answer

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.


With double ampersands:


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

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement