Case Statement - ORA-01427: single-row subquery returns more than one row-CodePudding

I need some help in my SQL script, it might be solved in other cases and I tried to apply it to my case, but no luck.

I have this big table "employee table" that contains all of the employee details employee_id, first_name, last_name, country(country_name), etc. and I also have this table "country table" that contains country_code, country_name

I want to get the country_code from the other table.

The country column in the employee table may or may not have a value and another condition is sometimes it has US or USA value that match to one value in the country table which is "US"

I have this script that I made already. I joined the two tables below in my subquery CASE statement but I'm having this error: "ORA-01427: single-row subquery returns more than one row"

Select distinct emp.employee_id, emp.first_name, emp.last_name
     , (select case
               WHEN emp.country = 'USA' then 'US'
               WHEN emp.country like '%' || c.country_name || '%' then c.country_code 
               ELSE 'US'
                END "COUNTRY_CODE" 
          from country c, employee emp
         WHERE  emp.country like '%' || c.country_name || '%'
       )
  from employee emp
;

I dunno how am I going to do it. Hoping somebody can help me, it would really mean a lot to me. Thanks!

CodePudding user response：

How about skipping the subquery which - obviously - returns more than a single row?

select e.employee_id, 
  e.first_name,
  e.last_name,
  case when e.country in ('US', 'USA') then 'US'
       else c.country_code
  end country_code
from employee e left join country c on instr(e.country, c.country_name) > 0;

By the way, if you used distinct, perhaps you should've put it into the subquery. Also, you should've correlated subquerie's country to main querie's employee, not introduce yet another employee table.

CodePudding user response：

select emp.employee_id, emp.first_name, emp.last_name, c.country as "COUNTRY_CODE"
from 
employee emp 
left join country c on c.country = decode(emp.country, 'USA', 'US')
group by emp.employee_id, emp.first_name, emp.last_name, c.country ;

There is a decode function in Oracle that can convert USA to US. Convert the USA to US and then match to the country table. I could not understand why your query is using % (unless there are partial match you have to). Based on my knowledge, group by is better than using distinct.