查询返回:ORA-01427 单行子查询返回多于一行 [英] Query returning : ORA-01427 Single-row subquery returning more than one row

问题描述

我正在尝试将以下 Sybase 查询转换为 Oracle 查询.

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;

但我尝试在转换后执行以下 Oracle 查询，但出现以下错误.

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);

推荐答案

你可以试试下面的，

我使用了 coalesce 以防 queue 列没有匹配项，它会将其视为空值，取而代之的是 2 值.

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);

这篇关于查询返回:ORA-01427 单行子查询返回多于一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！