Re: in statement not working

godiva wrote:
> Hi,
> I am seeing an odd problem with part of a stored procedure I am
> building. Values are passed via and Access interface. All values are
> processed properly except for one little annoying thing for which our
> DBAs have no explanation. I pass a string from access that looks like
> this: ABCDEF_ASDFGH. Inside the stored proc, it is altered to:
> 'ABCDEF','ASDFGH' so that it can be used as the details of an in
> statement. If only one is passed (i.e. ABCDEF), the in statement
works
> fine, but if it is more than one, it gets ignored. I have checked to
> make sure that the statement is properly built. Here is what it looks
> like, somewhat:
> listfromaccess : ABCDEF_ASDFGH
> listaltered: replace(listfromaccess, '_', chr(39)||chr(44)||chr(39))
>
> where table.item in (listaltered)
> We are on oracle 9i and currently use Toad 7.5 for most of
development.
> Any suggestions on how to rectify this situation would be greatly
> appreciated. I have also tried building the statement so that the
line
> becomes table.item = 'ABCDEF' or table.item = 'ASDFGH', but that also
> gets overlooked. It reports that the script is executed with 0 errors
> and 0 compile errors.
> thanks,
> godiva

1. Your replace statement will result in two missing ticks on either side like ABCDEF','ASDFGH.
2. IN operator does not take comma separated values from a variable/parameter... it will treat the variable as one entry. All entries MUST be explicitly separated by a comma i.e. WHERE this IN ('foo','foo1','foo2','...',...)
3. From the top of my head... your best bet may be using OR operator e.g. listaltered1 = 'ABCDEF'; listaltered2 = 'ASDFGH'; WHERE this = listaltered1 OR this = listaltered2 OR try dynamic sql... have not tested it though.

Regards
/Rauf Received on Thu Feb 10 2005 - 12:39:55 CST