JOLee JOLee - 1 year ago 60
SQL Question

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

This is the SQL statement:

ua.*, uuu.user_name
(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,
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 :


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

enter image description here

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download