Skip to Content

Subquery returned more than 1 value.

This query worked for more than a year. We have been using SAP Business One 9.2 Patch level 6 for the last 15 months. After the update to Patch level 10 we now are seeing this error with the query. Below is the query. Please advise what we need to change. Thank you.

SELECT DISTINCT T0.[DocNum] as 'Sales Order', T0.[CardName] as NAME, T1.[Dscription] as ITEMDESC, T1.[ItemCode] as 'Item', cast(round(T1.[Quantity],0) as int) as QTY, cast(round(T1.[OpenInvQty],0) as int) as 'OpenQTY', cast(round(T2.[CmpltQty],0) as int) as 'Complete', T0.[ReqDate] as 'CustReqDate'

, Case When T5.[Code] like 'TX%%' then T5.[Code] else '' end as 'TX#' , cast(round(T8.[OnHand],0)as int) as 'In Stock', T6.[Code] as 'LAMI'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'CN%' or S1.[Code] like 'WP17169%%')) as 'Control'

,(Select distinct S4.Code from ITT1 S3 Inner Join ITT1 S2 on S3.[Father] = S2.[Code] Inner Join ITT1 S4 on S4.[Father] = S2.code where S2.father = T1.[ItemCode] and (S2.[Code] like 'CN%' or S2.[code] like 'WP17169%%') and S4.Code like 'CV%') as 'Control2'

,(SELECT CASE WHEN S1.Code='CAN048000001' THEN 'YES' ELSE '' END FROM ITT1 S1 WHERE S1.[FATHER] = T1.[ITEMCODE] AND S1.[CODE] LIKE 'CAN%') AS 'AC1'

,(SELECT distinct S4.Code from ITT1 S3 Inner Join ITT1 S2 on S3.[Father] = S2.[Code] Inner Join ITT1 S4 on S4.[Father] = S2.code where S2.father = T1.[ItemCode] and (S2.[Code] like 'TX%%') and (S4.[Code] like 'M14744' or S4.[Code] like 'M3396' or S4.[Code] like 'M12997-1R' or S4.[Code] like 'M12997-2R' or S4.[Code] like 'M13496R' or S4.[Code] like 'WP03396-1' or S4.[Code] like 'WP06897' or S4.[Code] like 'WP07701%%' or S4.[Code] like 'WP07947A' or S4.[Code] like 'WP09040' or S4.[Code] like 'WP09056' or S4.[Code] like 'WP09449' or S4.[Code] like 'WP10080' or S4.[Code] like 'WP10195' or S4.[Code] like 'WP12220%%' or S4.[Code] like 'WP12284' or S4.[Code] like 'WP12659' or S4.[Code] like 'WP12997' or S4.[Code] like 'WP14990' or S4.[Code] like 'WP15815' or S4.[Code] like 'WP15829' or S4.[Code] like 'WP15830' or S4.[Code] like 'WP15849' or S4.[Code] like 'WP17392' or S4.[Code] like 'WP17392-1' or S4.[Code] like 'WP17700' or S4.[Code] like 'WP17723' or S4.[Code] like 'WP17724' or S4.[Code] like 'WP17725' or S4.[Code] like 'WP18294' or S4.[Code] like 'WP18589' )) as 'TX FOOT'

,(Select distinct S4.Code from ITT1 S3 Inner Join ITT1 S2 on S3.[Father] = S2.[Code] Inner Join ITT1 S4 on S4.[Father] = S2.code where S2.father = T1.[ItemCode] and (S2.[Code] like 'TX%%') and (S4.[Code] like 'M13496L' or S4.[Code] like 'M12997-1L' or S4.[Code] like 'M12997-2L' )) as 'TX FOOT2'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'WP07899A' or S1.[Code] like 'WP07935A' or S1.[Code] like 'WP11144A' or S1.[Code] like 'WP13423-1%%' or S1.[Code] like 'WP13423-3%%' or S1.[Code] like 'WP15413A' or S1.[Code] like 'WP16518W' or S1.[Code] like 'WP17641Z' )) as 'C-BACK'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'WP07901A' or S1.[Code] like 'WP11010-1A' or S1.[Code] like 'WP11700A' or S1.[Code] like 'WP12497A' or S1.[Code] like 'WP14847A' )) as 'C-BASE'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'WP07937A' or S1.[Code] like 'WP07898%%' or S1.[Code] like 'WP11271%%' or S1.[Code] like 'WP11699%%' or S1.[Code] like 'WP13329%%' or S1.[Code] like 'WP14833' or S1.[Code] like 'WP14845A' or S1.[Code] like 'WP14850A' or S1.[Code] like 'WP15858A' )) as 'C-FRONT'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'WP03412%%' or S1.[Code] like 'WP08683-1A' or S1.[Code] like 'WP10197-6' or S1.[Code] like 'WP11009-4A' or S1.[Code] like 'WP11307A' or S1.[Code] like 'WP11708A' or S1.[Code] like 'WP12423-9A' or S1.[Code] like 'WP12496A' or S1.[Code] like 'WP12657%%' or S1.[Code] like 'WP12992-9A' or S1.[Code] like 'WP13471R' or S1.[Code] like 'WP14081%%' or S1.[Code] like 'WP14335-1' or S1.[Code] like 'WP14511A' or S1.[Code] like 'WP14516A' or S1.[Code] like 'WP14567' or S1.[Code] like 'WP14635' or S1.[Code] like 'WP14639-1' or S1.[Code] like 'WP14669%%' or S1.[Code] like 'WP14708' or S1.[Code] like 'WP14709%%' or S1.[Code] like 'WP14834' or S1.[Code] like 'WP14844A' or S1.[Code] like 'WP14854A' or S1.[Code] like 'WP14909' or S1.[Code] like 'WP15310-1' or S1.[Code] like 'WP15485A' or S1.[Code] like 'WP17020A' or S1.[Code] like 'WP17657%%' )) as 'C-MAIN'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'WP15859A' )) as 'C-LT SIDE'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'WP15860A' )) as 'C-RT SIDE'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'WP07900A' or S1.[Code] like 'WP07936A' or S1.[Code] like 'WP10196%%' or S1.[Code] like 'WP11145A' or S1.[Code] like 'WP11310A' or S1.[Code] like 'WP12658A' or S1.[Code] like 'WP14199A' or S1.[Code] like 'WP12024-1A' or S1.[Code] like 'WP12170-1' or S1.[Code] like 'WP12424A' or S1.[Code] like 'WP12498A' or S1.[Code] like 'WP12991-3A' or S1.[Code] like 'WP13432-7A' or S1.[Code] like 'WP14634' or S1.[Code] like 'WP14846A' or S1.[Code] like 'WP14651' or S1.[Code] like 'WP14846%%' or S1.[Code] like 'WP14855A' or S1.[Code] like 'WP14864%%' or S1.[Code] like 'WP14910' or S1.[Code] like 'WP15312' or S1.[Code] like 'WP15414A' or S1.[Code] like 'WP15486A' or S1.[Code] like 'WP17021A' )) as 'C-TOP'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'M14228' or S1.[Code] like 'WP08618-2' )) as 'C-HANDLE'

,(Select S1.Code from ITT1 S1 where S1.[Father] = T1.[ItemCode] and (S1.[Code] like 'WP15415A' or S1.[Code] like 'WP14690A' )) as 'C-FEET/BRKT'

,T2.[DocNum] as 'Prod.Order', T2.[Status] as 'Status*', T2.[U_NBS_Priority]

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry]=T1.[DocEntry]

LEFT JOIN OWOR T2 ON T0.[DocNum]=T2.[OriginNum] AND T1.[ItemCode]=T2.[ItemCode]

INNER JOIN OITM T3 ON T1.[ItemCode]=T3.[ItemCode]

INNER JOIN ITT1 T5 ON T5.[Father]=T1.[ItemCode]

INNER JOIN ITT1 T6 ON T6.[Father]=T5.[Code]

INNER JOIN OITM T7 ON T6.[Code]=T7.[ItemCode]

INNER JOIN OITM T8 ON T5.[Code]=T8.[ItemCode]

WHERE T0.[DocStatus] = 'O' AND (T2.[Status] <> 'L' and T2.[Status] <> 'C') AND T1.[LineStatus] Like 'O' AND (T3.[QryGroup9]= 'Y' OR T3.[QryGroup10]='Y') AND (T5.[Code] like 'TX%%' or T5.[Code] like 'CN%' or T5.[Code] like 'WP17169%%') AND (T5.[Code] like 'TX%%' AND T7.[ItemName] like 'LAMINATION 2%%')

ORDER BY T2.[U_NBS_Priority], T0.[ReqDate]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Apr 30 at 06:23 AM

    Use distinct or top 1 in all the subquery and try

    Regards,

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded