postgresql 我使用这个命令并得到类似[305]的消息：单行查询返回多行：

j2qf4p5b 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(1)|浏览(97)

我使用这个命令并得到类似[305]的消息：单行查询返回多行：

with catregion as(
select ll.ID, crm.catid, crm.catname,
SUBSTR_REGEXPR('[^_]+' IN "REGIONNAME" OCCURRENCE 3) AS "attr_id", crm.attrname, ll.valint, ll.valreal, ll.valdate, valstr, vallong, VerNum
from CATREGIONMAP crm
join LLAttrData ll on ll.defid = crm.catid and ll.attrid = SUBSTR_REGEXPR('[^_]+' IN "REGIONNAME" OCCURRENCE 3)
WHERE attrname in ('Номер документа SAP','Статус OpenText','Статус документа (SAP)')
),
myselect as(
select DT.DATAID AS cardId,
(select VALSTR from  catregion a where a.catname = 'Атрибуты SAP' and a.attrname = 'Номер документа SAP' AND a.id = DT.DATAID) AS SAP_number,
(select VALSTR from  catregion a where a.catname = 'Договор_основные' and a.attrname = 'Статус OpenText' AND a.id = DT.DATAID) as OpenText_status,
(select VALSTR from  catregion a where a.catname = 'Договор_основные' and a.attrname = 'Статус документа (SAP)' AND a.id = DT.DATAID) as SAP_status
FROM DTREE DT)
SELECT * FROM myselect WHERE SAP_number IN ('SHP000000000000001110002850800000','SHD000000000000001120000682900000','SHP000000000000001110002738900000')

postgresql

来源：https://stackoverflow.com/questions/75210647/i-use-this-and-get-the-message-like-305-single-row-query-returns-more-than-on

1条答案

按热度按时间

ljsrvy3e1#

您需要通过添加额外的条件来排除多余的行，或者通过排序和获取TOP 1，使select VALSTR from catregion a ...子查询只返回一个值。
要查看导致此问题的确切原因，请运行子查询并查看哪个子查询返回多行：

select id, count(*) from catregion
where catname = 'Атрибуты SAP' and attrname = 'Номер документа SAP'
group by id having count(*) > 1;

select id, count(*) from catregion
where catname = 'Договор_основные' and attrname = 'Статус OpenText'
group by id having count(*) > 1;

select id, count(*) from catregion
where catname = 'Договор_основные' and attrname = 'Статус документа (SAP)'
group by id having count(*) > 1;

赞(0）回复(0）举报 2023-01-25

我来回答

postgresql 我使用这个命令并得到类似[305]的消息：单行查询返回多行：

1条答案

相关问题

热门标签

最新问答