Can you use an 'In' operator in a column expression?

Question

Using Oracle 11. Trying to write some simple code for a conceptual demo. To illustrate what I'm trying to achieve, imagine I have SOMETABLE that has two columns: ID and NAME, like so:

ID  NAME
---------
1   Tom
2   Larry
3   David
4   Steve

I'm trying to compute a third column that is true if the second column matches one of two hard-coded values. Something like this (which of course doesn't work.)

Select ID,
       NAME,
       (NAME in ('Larry', 'David')) as IS_FAVORITE
from SOMETABLE

and hoping to get this output...

ID  NAME   IS_FAVORITE
----------------------
1   Tom    FALSE
2   Larry  True
3   David  True
4   Steve  FALSE

Much to my surprise, I'm being told Oracle doesn't have the concept of booleans and I should be using 'numeric strings' or something like that, so this too is fine...

ID  NAME   IS_FAVORITE
----------------------
1   Tom    'N'
2   Larry  'Y'
3   David  'Y'
4   Steve  'N'

So can you use the IN operator in a column expression like this? If not, how would one compute the column that I am after?

`IN` is a condition, not a function. (It is not a function because Oracle doesn't have the Boolean data type; but it does have conditions. You just can't **return** the (Boolean) value of a condition for further processing.) — , May 02 '20 at 06:05
Yeah, figured that out by the below answer which worked like a champ! — Mark A. Donohoe, May 02 '20 at 06:06

score 1 · Accepted Answer · edited May 02 '20 at 13:16

1

You can achieve your expected output using case expression.

Select ID,
       NAME,
       CASE
        WHEN
            NAME in ('Larry', 'David')
        THEN
            'TRUE'
        ELSE
            'FALSE'
        END as IS_FAVORITE
from SOMETABLE

edited May 02 '20 at 13:16

answered May 02 '20 at 06:01

zealous

7,336
4
16
36

Oracle will complain that `FALSE` (and `TRUE` for that matter, but it will complain about `FALSE` first) are unknown identifiers. – May 02 '20 at 06:04
Perfect! Verbose, but perfect! (BTW, Oracle doesn't know what TRUE and FALSE are so I changed it to 'Y' and 'N') – Mark A. Donohoe May 02 '20 at 06:04
What you used is a `case` **expression**. PL/SQL also has `case` **statements** (similar to, but different in their nature from, `case` expressions). There are no `case` *statements* in plain SQL. – May 02 '20 at 06:11
Um... I'm confused. @mathguy said you actually used a case expression, and you originally had 'expression' in the text, but just updated it to 'statement' even though he said SQL doesn't have a statement. Huh?? [scratching head] – Mark A. Donohoe May 02 '20 at 07:26

Can you use an 'In' operator in a column expression?

1 Answers1