Query Error - Single row query returns more than one row

Question

I have a query where the input value is as: "Amar, Akbar, Anthony"

I want the query to treat input value as: 'Amar', 'Akbar', 'Anthony'. I have a regexp_substr which works in normal query and serves the purpose but when I put it with Case, it gives below error.

Single row query returns more than one row

The CASE is written so that if user doesn't enter anything in textbox, then query should return all rows, if user inputs something, result should show the matching values.

Select * from test_tbl a where 
     (
            CASE
              WHEN TRIM (:username) IS NULL
              THEN NVL (a.user_name, 'NOTHING')
              WHEN TRIM (UPPER (:username)) = 'NOTHING'
              THEN NVL (a.user_name, :username)
              ELSE UPPER (a.user_name)
            END) LIKE (
            CASE
              WHEN TRIM (:username) IS NULL
              THEN NVL (a.user_name, 'NOTHING')
              WHEN TRIM (UPPER (:username)) = 'NOTHING'
              THEN :username
              ELSE ((select regexp_substr(:username,'[^,]+', 1, level) from dual connect by regexp_substr(:username, '[^,]+', 1, level) is not null))
            END)

Is there a way to achieve required functionality? That is not change query much and include CASE with the regexp_substr.

Alex Poole · Accepted Answer · 2015-01-07T14:22:23.730

I'm not entirely sure I follow your logic, but I think this is what you're looking for:

select * from test_tbl a
where :username is null
or a.user_name in (
  select regexp_substr(:username,'[^,]+', 1, level) from dual
  connect by regexp_substr(:username, '[^,]+', 1, level) is not null
)

With some dummy data: and a bind variable:

create table test_tbl (user_name varchar2(10));

insert into test_tbl values ('Amar');
insert into test_tbl values ('Akbar');
insert into test_tbl values ('Joe');

var username varchar2(80);

When username is set

exec :username := 'Amar,Akbar,Anthony';

.. that query gets:

USER_NAME
----------
Amar       
Akbar

When it is null:

exec :username := null;

... that query gets all rows:

USER_NAME
----------
Amar       
Akbar      
Joe

I took the spaces out of your string of names so it would match properly. If your actual string from the textbox has spaces you'll need to handle them in the sub query.

Query Error - Single row query returns more than one row

1 Answers1