CODEWITHSUNDEEP
sqloracle-database
daxue
daxue

Reputation: 259

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

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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;

Upvotes: 3

Related Questions