JOLee JOLee - 5 months ago 8
SQL Question

Why is the same SQL sometimes report errors, some time it runs fine?

This is the SQL statement:

select
ua.*, uuu.user_name
from
(select
ud.dev_id,
(select uud.user_id as user_id
from user_device uud
where ud.dev_id = uud.dev_id and assigned = 1) user_id,
(select count(1)
from user_device du
where du.dev_id = ud.dev_id) user_number,
de.license
from
user_device ud
inner join
device de on ud.dev_id = de.dev_id
where ud.user_id = 'XXXXXX') ua
left join
user_info uuu on uuu.user_id = ua.user_id


Execute the same SQL, it sometimes reports this error, but sometimes it runs just fine.

The error :

error

and this is what I want (with another user_id yesterday)

enter image description here

Answer

The error is pretty self-explanatory. I'm pretty sure it is referring to this subquery:

 (select uud.user_id
  from user_device uud
  where ud.dev_id = uud.dev_id and assigned = 1
 )

Clearly, this subquery is returning multiple rows under some circumstances. A quick and dirty fix is to add and rownum = 1 to the where clause.

You can determine where the duplicates are by running:

select uud.dev_id, count(*) as cnt
from user_device uud
where uud.assigned = 1
group by uud.dev_id
having count(*) > 1;