单行子查询返回多行 - 查找说明/解决方案

Question

长篇文章在这里...振作。。。

我创建了以下 4 个查询，这些查询每天按顺序运行。创建这些是为了让我的公司能够扫描工具的进出服务，而不是手动输入日期。显示出来时，计划无法计划使用该工具的作业。这些正常运行，只要一个字段为空(SERVICE_DATE_IN)，就不会有任何问题：

查询 1：扫描出的工具

UPDATE PMEQMT P
SET SERVICE_DATE_OUT = (SELECT MAX(TL.TRANS_DATE)
FROM TRANSLOG TL
WHERE P.EQNO = TL.ITEMNO AND
TL.LOC_DESC = 'E-OUT OF SERVICE' AND
TL.TRANS_DATE >= SYSDATE - 1 AND
TL.TRANS_IN_OUT = 'IN'
)
WHERE P.CLASS = 'TL' AND
P.SERVICE_DATE_OUT IS NULL

查询 2：扫描的工具

UPDATE PMEQMT P
SET SERVICE_DATE_IN = (SELECT MAX(TL.TRANS_DATE)
FROM TRANSLOG TL
WHERE P.EQNO = TL.ITEMNO AND 
TL.LOC_DESC = 'E-IN SERVICE' AND
TL.TRANS_DATE >= SYSDATE - 1 AND
TL.TRANS_IN_OUT = 'IN'
)
WHERE P.CLASS = 'TL' AND
P.SERVICE_DATE_IN IS NULL

查询 3：擦除服务日期以防未清除

UPDATE PMEQMT
SET SERVICE_DATE_IN = NULL
WHERE SERVICE_DATE_OUT IS NULL AND
SERVICE_DATE_IN IS NOT NULL AND
CLASS = 'TL'

查询 4：工具又回来了，出/入日期均为空

UPDATE PMEQMT
SET SERVICE_DATE_OUT = NULL,
SERVICE_DATE_IN = NULL 
WHERE SERVICE_DATE_OUT IS NOT NULL AND
SERVICE_DATE_IN IS NOT NULL AND
CLASS = 'TL'

工程部门要求能够手动输入SERVICE_DATE_IN日期。所以我提出了以下疑问：

查询 1：与上面的查询 1 相同

查询2：这是我的问题查询。目前我收到错误：

ORA-01427：单行子查询返回多行

当我进行测试并且只将一个P.EQNO(项目)移动到TL.LOC_DESC(位置)时，此查询有效。我开始测试将其他P.EQNO(项目)移动到不同的位置，然后开始收到错误。

有没有人能够解释这里发生的事情，如果你感觉特别好，请帮我修改查询以解决错误？

UPDATE PMEQMT P
SET SERVICE_DATE_IN =
CASE 
WHEN SERVICE_DATE_IN IS NULL THEN (SELECT MAX(TL.TRANS_DATE)
FROM TRANSLOG TL
WHERE P.EQNO = TL.ITEMNO AND 
TL.LOC_DESC = 'E-IN SERVICE' AND
TL.TRANS_DATE >= SYSDATE - 1 AND
TL.TRANS_IN_OUT = 'IN'
)
WHEN (TRUNC(SERVICE_DATE_IN)) <= (TRUNC(SYSDATE)) THEN (SELECT ((TRUNC(SYSDATE))+1)
FROM TRANSLOG TL
WHERE P.EQNO = TL.ITEMNO AND
TL.LOC_DESC = 'E-OUT OF SERVICE'
)
WHEN (TRUNC(SERVICE_DATE_IN)) > (TRUNC(SYSDATE)) THEN (SELECT SERVICE_DATE_IN 
FROM TRANSLOG TL
WHERE P.EQNO = TL.ITEMNO AND
TL.LOC_DESC = 'E-OUT OF SERVICE'
)
END
WHERE CLASS = 'TL'

查询 3：没有问题

UPDATE PMEQMT P 
SET P.SERVICE_DATE_OUT = NULL, P.SERVICE_DATE_IN = NULL 
WHERE EXISTS (SELECT 1 FROM TRANSLOG TL WHERE TL.ITEMNO = P.EQNO AND TL.LOC_DESC = 'E-IN SERVICE')

如果您发现我的代码有任何问题或有办法改进它，我会敞开心扉！

--------------------编辑-------------------------

多亏了@GMB我有一个正确的工作查询。我现在遇到了两个相同的 WHEN 条件的问题。

UPDATE PMEQMT P 
SET SERVICE_DATE_IN = CASE 
WHEN SERVICE_DATE_IN IS NULL 
THEN (
SELECT MAX(TL.TRANS_DATE)
FROM TRANSLOG TL
WHERE 
P.EQNO = TL.ITEMNO
AND TL.LOC_DESC = 'E-IN SERVICE' 
AND TL.TRANS_DATE >= SYSDATE - 1 
AND TL.TRANS_IN_OUT = 'IN'
)
WHEN 
TRUNC(SERVICE_DATE_IN) <= TRUNC(SYSDATE) 
AND EXISTS (        
SELECT 1
FROM TRANSLOG TL
WHERE 
P.EQNO = TL.ITEMNO 
AND TL.LOC_DESC = 'E-OUT OF SERVICE'
)
THEN TRUNC(SYSDATE) +1        
WHEN TRUNC(SERVICE_DATE_IN) > TRUNC(SYSDATE) 
THEN (
SELECT MAX(SERVICE_DATE_IN)
FROM TRANSLOG TL
WHERE 
P.EQNO = TL.ITEMNO
AND TL.LOC_DESC = 'E-OUT OF SERVICE'
)
WHEN TRUNC(SERVICE_DATE_IN) > TRUNC(SYSDATE)
THEN (
SELECT MAX(TL.TRANS_DATE)
FROM TRANSLOG TL
WHERE 
P.EQNO = TL.ITEMNO
AND TL.LOC_DESC = 'E-IN SERVICE' 
AND TL.TRANS_DATE >= SYSDATE - 1 
AND TL.TRANS_IN_OUT = 'IN'
)
END
WHERE CLASS = 'TL'

我正在测试的记录保留了MAX(SERVICE_DATE_IN)数据。我在这里有什么选择？我打算做一个嵌套的 CASE，但我会遇到同样的问题。我可以做一个单独的查询，在这个查询之后运行，纯粹查看最后一组条件。我只是想知道是否可以在同一查询中完成。这真的取决于TL。LOC_DESC值。

Answer 1

您需要某种逻辑来将子查询返回的记录数限制为仅一个。

在第一个子查询(这是查询的第一个版本的一部分)中，这是通过使用不带GROUP BY子句的聚合提供的：

SET SERVICE_DATE_IN = (
SELECT MAX(TL.TRANS_DATE)
FROM TRANSLOG TL
WHERE 
P.EQNO = TL.ITEMNO
AND TL.LOC_DESC = 'E-IN SERVICE'
AND TL.TRANS_DATE >= SYSDATE - 1
AND TL.TRANS_IN_OUT = 'IN'
)

第二个子查询，其中返回值基本上是TRUNC(SYSDATE) +1，可以在CASE语句的相关WHEN部分中移动到NOT EXISTS条件。

第三个子查询可以使用聚合进行修复。

考虑：

UPDATE PMEQMT P 
SET SERVICE_DATE_IN = CASE 
WHEN SERVICE_DATE_IN IS NULL 
THEN (
SELECT MAX(TL.TRANS_DATE)
FROM TRANSLOG TL
WHERE 
P.EQNO = TL.ITEMNO
AND TL.LOC_DESC = 'E-IN SERVICE' 
AND TL.TRANS_DATE >= SYSDATE - 1 
AND TL.TRANS_IN_OUT = 'IN'
)
WHEN 
TRUNC(SERVICE_DATE_IN) <= TRUNC(SYSDATE) 
AND EXISTS (        
SELECT 1
FROM TRANSLOG TL
WHERE 
P.EQNO = TL.ITEMNO 
AND TL.LOC_DESC = 'E-OUT OF SERVICE'
)
THEN TRUNC(SYSDATE) +1        
WHEN TRUNC(SERVICE_DATE_IN) > TRUNC(SYSDATE 
THEN (
SELECT MAX(SERVICE_DATE_IN)
FROM TRANSLOG TL
WHERE 
P.EQNO = TL.ITEMNO
AND TL.LOC_DESC = 'E-OUT OF SERVICE'
)
END
WHERE CLASS = 'TL'

Answer 2

问题查询中的最后一个案例(如下所示)是否返回多行？不应该。如果此行返回多行，则查询将失败。

SELECT SERVICE_DATE_IN 
FROM TRANSLOG TL
WHERE P.EQNO = TL.ITEMNO AND
TL.LOC_DESC = 'E-OUT OF SERVICE'