I use this and get the message like [305]: single-row query returns more than one row:-CodePudding

I use this and get the message like [305]: single-row query returns more than one row:

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

CodePudding user response：

You need to make your select VALSTR from catregion a ... subqueries to return only one value by adding additional conditions to exclude the extra lines or by sorting and taking TOP 1.

To see what exactly is causing the issue run the subqueries and see which one returns more than one row:

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;