Re: dynamic SQL question

DiggidyMack69 wrote:
> 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

Evening DM!

Usually two quotes represent 1 escaped quote. In you r last, concatenated example, you start off using three, and end using three.
And you can always use a chr() instead of '. It's 39 I think. chr(39) =? "'"

SQL> select chr(39) from dual;
C
-
'
Looks that way...

Frank Received on Wed Feb 05 2003 - 21:36:02 CET