Select case statement: single-row subquery returns more than one row

0
votes

I'm trying to determine wether or not an employee is a member of a specific usergruop where the word 'contractor' is a part. All employees can be part of more than one usergroup, hence the error I get.

This is my select case statement:

SELECT CASE WHEN
(SELECT usergroup FROM usergr
WHERE usergroupnumber = (SELECT usergroupnumber FROM userlist 
WHERE username='Carl')) 
LIKE '%contractor%' THEN 1 
ELSE 0 
END FROM DUAL

Now it is this subquery which return more than 1 row because Carl is a member of more than 1 usergroup. How can i check if one of these usergroups has the 'contractor' in it.

UPDATE: Please let me provide you guys with some additional info on this.

This is to be used in a document management program. In this program I can create different attributes that is attached to all documents. And in these attributes I can place values based on select statements. So for example I have created an attribute which adds the projectnumber value to documents created in various Projects.

For my issue as described above I want an attribute that either hold the value 0 or 1. This will control wether or not a user can perform certain tasks in the program, when handling a file.

Example

The select case statement I have written above 'almost' works. Because if you change 'Carl' with 'John' or 'Brian' I will get a correct value, because these are members of only one usergroup. But with 'Carl' or 'James' I get the error as they are part of more than one usergroup.

If you use the 'Example' I've given, I want the following result:

  • When 'John' value = 0
  • When 'Carl' value = 1
  • When 'James' value = 1
  • When 'Brian' value = 0
2
sqloracleselectcase
Please tell us the names of the relevant tables and columns. We can reverse engineer that, but it takes more effort (and from each volunteer separately instead of you doing the work once for all of us) compared to you just telling us. - mathguy
Use EXISTS. - PM 77-1

2 Answers

0
votes

I'm not a 100% sure how the query is to be used but the following will return 1 where there is a user called Carl who is joined to a group with the 'contractor' as part of the usergroup. 

SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
          FROM usergr ug
               INNER JOIN userlist ul
               ON ug.usergroupnumber = ul.usergroupnumber
          WHERE ug.usergroup LIKE '%contractor%'
          AND   ul.username = 'Carl')

All that really happens here is that the potentially multiple results from joining the usergr table to the userlist table are resolved to a simple true(1)/false(null) result by the EXISTS clause.

0
votes

Trying to guess something about your tables, you may need something like this:

with userlist(username, usergroupnumber) as (
    select 'Carl', 1 from dual union all
    select 'Carl', 2 from dual union all
    select 'John', 2 from dual
),
usergr (usergroupnumber, usergroup) as (
    select 1, '__contractor__' from dual union all
    select 2, 'XXXXXXXXXXXXXX' from dual union all
    select 3, 'YYYYYYYYYYYYYY' from dual
)
SELECT case
        when count(1) = 0
          then 0
        else 1
       end
FROM userlist ul
       inner join usergr ug
         on ( ug.usergroupnumber = ul.usergroupnumber)
WHERE ul.username='Carl'
  and usergroup like '%contractor%'

Notice that you do not need DUAL, but you can simply apply some logic on the result of a count to get a "boolean" value (0/1)