CODEWITHSUNDEEP
sqloracle-databasesql-update
saloni
saloni

Reputation: 47

Query returning : ORA-01427 Single-row subquery returning more than one row

I was trying convert below Sybase query to Oracle query.

update Product set pd.age = (case when pd.exittime!= null then (sysdate - 
pd.exittime)  
else ( case when pd.queue = dp.queue 
then (select (sysdate - pd.entrytime) from department dp1 where pd.id = 
dp1.id ) else 2 END) END)
from Product pd,department dp
where pd.id > 1
AND pd.id = dp.id
AND pd.status in('1','7','2','5')
AND pd.currentstatus = dp.currentstatus 
AND pd.activity= dp.activity;

But I have tried executing below Oracle query after conversion but getting following error.

update Product pd set pd.age = (select (case when pd.exittime!= null then 
(sysdate - pd.exittime)   
else ( case when pd.queue = dp.queue 
then (select (sysdate - pd.entrytime) from department dp1 where pd.id = dp1.id 
 ) else 2 END) END)
from department dp
where pd.id > 1
AND pd.id = dp.id
AND pd.status in('1','7','2','5')
AND pd.currentstatus = dp.currentstatus 
AND pd.activity= dp.activity) 
where exists 
(select 1 from department dp
where pd.id > 1
AND pd.id = dp.id
AND pd.status in('1','7','2','5')
AND pd.currentstatus = dp.currentstatus 
AND pd.activity= dp.activity);

Upvotes: 0

Views: 83

Answers (2)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

Could you try with below,

I have used coalesce in case there is no match for column queue it will consider it as null and value 2 is taken instead.

update product pd
set    pd.age = case
                   when pd.exittime != null then
                    (sysdate - pd.exittime)
                   else
                    coalesce((select (sysdate - pd.entrytime)
                                from department dp
                                where pd.queue = dp.queue
                                  and pd.id = dp.id)
                            ,2)
                end
where  pd.id > 1
and    pd.status in ('1','7','2','5')
and    exists (select 1
                 from department dp
                where pd.id = dp.id
                  and pd.currentstatus = dp.currentstatus
                  and pd.activity = dp.activity);

Upvotes: 1

Atif
Atif

Reputation: 2210

As suggested in earlier answer please use key preserved view to convert such type of Sybase update queries to Oracle, as Oracle dont allow JOINs in update queries.

UPDATE (SELECT pd.age as age,
              pd.id,
              dp.id,
              pd.currentstatus,
              dp.currentstatus,
              pd.activity,
              dp.activity,
              pd.status,
              pd.exittime,
              pd.queue,
              dp.queue
              FROM Product pd INNER JOIN department dp
        ON (pd.id = dp.id AND pd.currentstatus = dp.currentstatus AND pd.activity= dp.activity)
        WHERE pd.id > 1 AND pd.status in('1','7','2','5'))
SET pd.age = CASE WHEN pd.exittime!= null THEN 1 
ELSE ( CASE WHEN pd.queue = dp.queue 
            THEN sysdate - pd.exittime
            ELSE 2 END) 
    END;

Upvotes: 0

Related Questions