IN Cluase with Bind Variable

[ekta22]

Hi,

I am trying to use the IN clause with my bind variable but with no success.

Any ideas on how to do this?

Thanks,

-E

 

[SantaMufasa]

Ekta,

Show us what your code looks like now, and we can post code that will work for you. (Without code, we have no clue what to suggest. <smile>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ekta22]

If I try a simple select statement with my bind variable I do get data with just one value but if I enter multiple values I don't get any results. I m using A bind variable where user can enter multiple values for that parameter.

Select * from table where type IN :1

But if I try the statement below without the bind variable I get the right results
Select * from table where type IN ('AA', 'BB')

 

[SantaMufasa]

Ekta,

To achieve your desired result, instead of using the "IN" operator as you have done, I recommend your using the "LIKE" operator as I have done below:

select * from ekta;

TYPE
-----
CC
DD
BB
AA

exec :X := 'AA,BB'
select * from ekta where :x like '%'||type||'%';

TYPE
-----
BB
AA

Let us know if this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[karluk]

Don't use SantaMufasa's suggestion. It has the same flaws as a similar suggestion he made in thread186-1470738. SantaMufasa's code will generate spurious matches. For example, with :X := 'AA,BB' as above, you will get matches on rows that contain TYPE = 'AA', 'BB', 'A', and 'B'. In all probability you don't want the matches on 'A' and 'B', since they are only substrings of the values you are searching for.

It is much better to build a solution similar to Tom Kyte's suggestion, the one mentioned in thread186-1470738.

 

[BJCooperIT]

I prefer a simple all-purpose function like:

CREATE OR REPLACE FUNCTION f_string_in_list_yn
(
    p_search_string     IN VARCHAR2,
    p_argument_list     IN VARCHAR2,
    p_delimiter_char    IN CHAR DEFAULT ','
)
    RETURN CHAR
IS
    v_delimiter_char    CHAR(01) := nvl(substr(p_delimiter_char,1,1),',');
BEGIN
    /*====================================================*/
    --  Wrap delimiter around the values: AA becomes ,AA,
    --  and then see if the search string is in the list
    /*====================================================*/
    IF  instr(  v_delimiter_char||p_argument_list||v_delimiter_char,
                v_delimiter_char||p_search_string||v_delimiter_char) > 0 
    THEN
        RETURN('Y');    --Value is in the list
    ELSE
        RETURN('N');    --Value is NOT in the list
    END IF;
END f_string_in_list_yn;

SQL> create table my_tab (text varchar2(05));
 
Table created
 
SQL> insert into my_tab values ('AA');
 
1 row inserted
 
SQL> insert into my_tab values ('A');
 
1 row inserted
 
SQL> insert into my_tab values ('BB');
 
1 row inserted
 
SQL> insert into my_tab values ('B');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select text, f_string_in_list_yn(text,'A,AA,BB') yn from my_tab;
 
TEXT  YN
----- --------------------------------------------------------------------------------
AA    Y
A     Y
BB    Y
B     N
 
SQL>

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Barb,

I, of course, prefer the simplicity of your function, over the complexities of Tom Kyte's example (albeit his solution is very clever).

To solve the original need that Ekta presented, what value do you assign to the bind variable to produce "Y" values on both the "AA" and "BB" rows, using your code?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[BJCooperIT]

Of course you are correct. I neglected to post how Ekta could use the function in his/her scenario.

--
--Original Poster's Examples: 
--Select * from table where type IN :1
--Select * from table where type IN ('AA,BB')
--
--Test on MY_TAB:
--
SQL> SELECT *
  2  FROM   my_tab
  3  WHERE  f_string_in_list_yn(text,'AA,BB') = 'Y';
 
TEXT
-----
AA
BB

Ekta could use:

SELECT *
FROM   table
WHERE  f_string_in_list_yn(type,'AA,BB') = 'Y';

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

I'm sorry I wasn't clear, Barb...I meant that I wasn't able to get proper results using a bind variable as the argument in your function. Could you please illustrate population of a bind variable that produces Ekta's desired results?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[karluk]

I haven't tested BJ's suggestion, but at first glance it looks remarkably similar to the revisions SantaMufasa made to his code in thread186-1470738 after I pointed out the problem with spurious matches. If this is indeed the case, I'm afraid it isn't really a good alternative to Tom Kite's solution. The problem is that it doesn't entirely eliminate the problem with spurious matches. In particular, you need to know ahead of time that the delimiter character doesn't appear in the data being searched. If it does, you could (and very possibly will) get spurious matches on rows that contain, say, ',AA,' as a substring of a longer string.

I see the BJ incorporates SantaMufasa's other suggestion to parameterize the delimiter character in an attempt to provide a work-around to this issue with spurious matches. But it still doesn't solve the basic issue that, in order to get the search to work properly, you have to know ahead of time a lot of detail about the contents of potentially millions of rows of data. But I would say a search function has limited value if it only works when you have advanced knowledge of the data you're searching.

That's especially true since we already know about Tom Kyte's solution, which doesn't have this fundamental limitation. No doubt there may be other approaches that work as well or better than Tom Kyte's, but so far we haven't come up with any. Until that happens, I recommend against trying to reinvent the wheel and go instead with the proven solution.