Re: dynamic SQL question

This issue has recently been discussed here. This link should help you.

http://groups.google.com/groups?selm=130ba93a.0301271353.7e33e857%40posting.google.com&output=gplain

• Jusung Yang

DiggidyMack69_at_hotmail.com (DiggidyMack69) wrote in message news:<c86ce4f.0302050816.3c027291_at_posting.google.com>...
> Hello all, I am probably missing something simple here but this
> driving me bonkers. I have a variable we will call mtype. I have a
> pl/sql
> program that will change this variable to the string to be passed to a
> statement like the following.
> I need to change the variable to a list like '1','2','4','6'.
> By hardcoding either WHERE type IN (1,2,4,6) or WHERE type IN
> ('1','2','4','6') this statement runs fine. The problem I am having
> is getting this string in the variable properly so that SQL will
> recognize it properly.
>
> I have tried(as well as using concat in a couple variations) but these
> no rows because for some reason SQL sees this as one item for the IN
> statement rather than the string for the IN statement.
>
> '1'||','||'2'||','||'4'||','||'6'
>
> '''1'',''2'',''4'',''6'''
>
>
> The column type is VARCHAR(2)
>
> SELECT
> state,
> max(product)as prodmax
> FROM table1a
> WHERE type IN (&mtype)
> AND year = 2001
> GROUP BY state;
>
> Thanks in adavance!!!
> DM
Received on Thu Feb 06 2003 - 01:22:12 CET