Defining a variable taking on multiple values

Question

I want to define some kind of local variable that takes on a list of values, so that I can reference that list in a query. I have a field, "my_field", and I want to write a query that returns 1 when "my_field" takes on a value of 'term1', 'term2', or 'term3', and returns 0 when it takes on any other value.

Here's what I've attempted:

DEFINE my_variable IN ('term1', 'term2', 'term3');

SELECT
   CASE WHEN my_field IN '&my_variable' THEN 1 ELSE 0 END AS 'new_field'
FROM my_table

This doesn't work though, I get syntax errors. I'm using Oracle's SQLDeveloper. Any help would be appreciated.

What syntax errors do you get?

Alex Poole
– Alex Poole

2018-08-28 22:16:58 +00:00
Commented Aug 28, 2018 at 22:16 — Alex Poole
– Alex Poole, Commented Aug 28, 2018 at 22:16

Alex Poole · Accepted Answer · 2018-09-06 17:06:50Z

1

I can see a few issues off-hand.

you are missing an equals sign when defining your variable;
your substitution variable reference should not be in single quotes;
you are repeating the IN keyword;
the column expression alias should also not be in single quotes.

DEFINE my_variable = IN ('term1', 'term2', 'term3')

SELECT
   CASE WHEN my_field &my_variable THEN 1 ELSE 0 END AS new_field
FROM my_table

Running that with set verify on shows the final, valid, query it generates:

old:SELECT
   CASE WHEN my_field &my_variable THEN 1 ELSE 0 END AS new_field
FROM my_table
new:SELECT
   CASE WHEN my_field IN ('term1', 'term2', 'term3') THEN 1 ELSE 0 END AS new_field
FROM my_table

 NEW_FIELD
----------
         1

I have many terms that I am putting into the variable, and I am adding line breaks to help keep the code clean. However, when I do this, it causes the query to error.

If you want to split the define into multiple lines you can use the - continuation character, but in SQL Developer you need to actually make it a comment for it to work properly:

DEFINE my_variable = IN ( --
  'term1', --
  'term2', --
  'term3')

SELECT
   CASE WHEN my_field &my_variable THEN 1 ELSE 0 END AS new_field
FROM my_table
/

which set verify on shows as:

old:SELECT
   CASE WHEN my_field &my_variable THEN 1 ELSE 0 END AS new_field
FROM my_table

new:SELECT
   CASE WHEN my_field IN ( --
  'term1', --
  'term2', --
  'term3') THEN 1 ELSE 0 END AS new_field
FROM my_table

 NEW_FIELD
----------
         1

edited Sep 6, 2018 at 17:06

answered Aug 28, 2018 at 22:20

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nathan Calverley Over a year ago

Thanks for trying, but this unfortunately did not work. I'm getting the error "invalid relational operator"

Alex Poole Over a year ago

Did you add the missing equals sign; and are you running as a script or as a statement? I've run exactly as shown (as a script) and shown the result I got. To get that error I think you would have to either run as a script with a malformed define (which errors), or run as a statement; and either way not enter anything when promoted for the substitution value?

Alex Poole Over a year ago

It's also possible you've changed the define character, or done set define off. Run show define in that session and check what it says. You may need to turn it back on and/or set to ampersand.

Nathan Calverley Over a year ago

Okay I've nearly got it working with your solution, there's just one thing that's breaking. When I run your code from your original answer, it works. However, I have many terms that I am putting into the variable, and I am adding line breaks to help keep the code clean. However, when I do this, it causes the query to error. Any idea how I can use your solution with line breaks separating the terms?

Alex Poole Over a year ago

@NathanCalverley - I've added a version with line breaks, hopefully that is what you meant.

|

Collectives™ on Stack Overflow

Defining a variable taking on multiple values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related