0

I am running the following query but it is not working

select * From  table r where account_id = 1111 and phone_number 
in (   :phone ) 

I passed these values

(1111111111,2222222222,3333333333)

But it did not return any result. If I pass one of them only then it did return the result.

2
  • 1
    There are various ways to tackle this, but first, where do the individual values come from and how are you constructing the single bind value (CSV string)? If you can use a collection instead of a string then it will be simpler; or if they are coming from the same database then you might be able to avoid using a variable at all. Commented Aug 27, 2021 at 15:45
  • Values come from the GUI Commented Aug 27, 2021 at 15:52

1 Answer 1

2

You can use:

select /*+ full(r) parallel(r,8) */
       *
From   table_name r
where  account_id = 1111
and    ','||:phone:||',' LIKE '%,'||phone_number||',%'

As to your query:

But it did not return any result. If I pass one of them only then it did return the result.

A bind variable is a single string and not a list of strings so your query is the equivalent of:

select /*+ full(r) parallel(r,8) */
       *
From   table_name r
where  account_id = 1111
and    phone_number in ( '1111111111,2222222222,3333333333' );

and is not your expected query of:

select /*+ full(r) parallel(r,8) */
       *
From   table_name r
where  account_id = 1111
and    phone_number in ( '1111111111','2222222222','3333333333' );

Since the table does not contain a phone number that matches the string '1111111111,2222222222,3333333333' then it returns zero rows.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.