Using string variables in Select statements

#1

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

#2

okumura-mikiop (AT) jvc-victor (DOT) jp wrote:

Quote:

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

There is a demo of how to do it in Morgan's Library at www.psoug.org
Click on Morgan's Library under Resources on the left.
Click on Conditions
Scroll down to "Complex IN Demo."

Some of what it demos is what does NOT work so run the complete demo
and then use what works.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

okumura-mikiop (AT) jvc-victor (DOT) jp wrote:

Quote:

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

There is a demo of how to do it in Morgan's Library at www.psoug.org
Click on Morgan's Library under Resources on the left.
Click on Conditions
Scroll down to "Complex IN Demo."

Some of what it demos is what does NOT work so run the complete demo
and then use what works.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

okumura-mikiop (AT) jvc-victor (DOT) jp wrote:

Quote:

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

There is a demo of how to do it in Morgan's Library at www.psoug.org
Click on Morgan's Library under Resources on the left.
Click on Conditions
Scroll down to "Complex IN Demo."

Some of what it demos is what does NOT work so run the complete demo
and then use what works.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

okumura-mikiop (AT) jvc-victor (DOT) jp wrote:

Quote:

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

There is a demo of how to do it in Morgan's Library at www.psoug.org
Click on Morgan's Library under Resources on the left.
Click on Conditions
Scroll down to "Complex IN Demo."

Some of what it demos is what does NOT work so run the complete demo
and then use what works.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

On May 1, 4:07 pm, okumura-mik... (AT) jvc-victor (DOT) jp wrote:

Quote:

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

http://tkyte.blogspot.com/2006/06/varying-in-lists.html
http://www.williamrobertson.net/docu...separated.html

#7

On May 1, 4:07 pm, okumura-mik... (AT) jvc-victor (DOT) jp wrote:

Quote:

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

http://tkyte.blogspot.com/2006/06/varying-in-lists.html
http://www.williamrobertson.net/docu...separated.html

#8

On May 1, 4:07 pm, okumura-mik... (AT) jvc-victor (DOT) jp wrote:

Quote:

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

http://tkyte.blogspot.com/2006/06/varying-in-lists.html
http://www.williamrobertson.net/docu...separated.html

#9

On May 1, 4:07 pm, okumura-mik... (AT) jvc-victor (DOT) jp wrote:

Quote:

I am attempting to use a variable in the WHERE clause of a SELECT statement.
How would one pass a value for an IN statement, ie.

SELECT *
FROM inventory
WHERE color IN ('red','green')

If I replaced the last line with

WHERE color IN (v_color)

How would I set it?

v_color := '"red","green"'

doesn't work.

http://tkyte.blogspot.com/2006/06/varying-in-lists.html
http://www.williamrobertson.net/docu...separated.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Using string variables in Select statements

comp.databases.oracle.misc comp.databases.oracle.misc

Using string variables in Select statements - 05-01-2008 , 10:07 AM

Re: Using string variables in Select statements - 05-01-2008 , 11:30 AM

Re: Using string variables in Select statements - 05-01-2008 , 11:30 AM

Re: Using string variables in Select statements - 05-01-2008 , 11:30 AM

Re: Using string variables in Select statements - 05-01-2008 , 11:30 AM

Re: Using string variables in Select statements - 05-03-2008 , 02:22 AM

Re: Using string variables in Select statements - 05-03-2008 , 02:22 AM

Re: Using string variables in Select statements - 05-03-2008 , 02:22 AM

Re: Using string variables in Select statements - 05-03-2008 , 02:22 AM