Re: SQL question

Chinna wrote:
>
> Try this :
>
> select * from tbl_name
> where numtochar(number_column) in (host_var_of_str_type);
>
> Let me know if this helped,
> Chinna

IMHO, it is bad manners to suggest "solutions" which do not work and which can be shown not to work with only a very little effort.

>
> curtis_strain_at_mail.amsinc.com wrote in article
> <875664052.9905_at_dejanews.com>...
> > I am trying to do the following sql statement:
> >
> > SELECT * from tbl_name
> > WHERE number_column IN (host_variable_of_string_type);
> >
> > I got syntax error. The host_variable_of_string_type variable looks
 like:
> > "1,2"
> >
> > How can I do this? Thanks in advance for any suggestions!
> >
> > -------------------==== Posted via Deja News ====-----------------------
> > http://www.dejanews.com/ Search, Read, Post to Usenet
> >

A host variable (of any datatype) may only contain a single value, not a list.
In SQL*Plus you could put the value '(1,2)' into a *string-substitution* variable, i.e DEFINE xx = '(1,2)', then use: SELECT * from tbl_name
WHERE number_column IN &xx

This works because the string-substitution variable is substituted before the statement is submitted to the parser. The same method can also be used in a Pro* interface by building the statement with the appropriate string before execution.

I don't know of any simple solution in PL/SQL, except to use your list to populate a temporary table and to re-write the main query as a SELECT ...
FROM ... WHERE <column> in (subquery)

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"