Buen día para todos,
Tengo un query de comisiones que me solicitó el área de contabilidad, el cual es el siguiente:
SELECT DISTINCT
T4.[SeriesName],
T0.[DocNum],
T0.[DocDate],
T0.[CardName],
(SELECT MAX(T9.[SlpName]) FROM OSLP T9 INNER JOIN OINV T8 ON T9.[SlpCode] = T8.[SlpCode]
WHERE T8.[DocEntry] = T6.[DocEntry]) AS "Vendedor",
T5.[SumApplied] 'Valor Pagado',
T5.[DcntSum],
MAX(T0.[DocTotal]) AS "Valor Documento",
T6.[DocDate] AS "Fecha Factura",
(SELECT T8.[SeriesName] FROM NNM1 T8 INNER JOIN OINV Y ON Y.[Series] = T8.[Series] WHERE Y.[DocEntry] = T6.[DocEntry]) as 'Serie',
T6.[DocNum] AS "Número de Factura", T6.[BaseAmnt], T6.[DocTotal],
(SELECT SUM(P0.[StockPrice]*P0.[Quantity]) FROM INV1 P0 WHERE T6.[DocEntry] = P0.[DocEntry]) AS 'Costo',
CAST(T0.[DocDate]-T6.[DocDate] AS INT) AS "Días de diferencia"
FROM ORCT T0
LEFT OUTER JOIN JDT1 T3 ON T0.[TransId] = T3.[TransId]
INNER JOIN NNM1 T4 ON T0.[Series] = T4.[Series]
INNER JOIN RCT2 T5 ON T0.[DocEntry] = T5.[DocNum]
INNER JOIN OINV T6 ON T5.[DocEntry] = T6.[DocEntry]
INNER JOIN INV1 T7 ON T6.[DocEntry] = T7.[DocEntry]
WHERE T0.[DocDate] BETWEEN [%0] AND [%1] AND (T3.[Debit] >0 or T3.[Credit] >0) AND NOT (T3.[Account] = '13551501' OR T3.[Account] = '13551701' OR T3.[Account] = '135518')
GROUP BY
T4.[SeriesName], T0.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardName], T5.[SumApplied], T5.[DcntSum], T6.[DocDate], T6.[DocEntry], T6.[DocNum], T6.[BaseAmnt], T6.[DocTotal]
El query hasta este punto es funcional y obtiene los datos que contabilidad me solicitó.
Ahora me pidieron que debo añadir en base a las facturas que me arroja este query, cuales tienen notas crédito, por lo que opté por añadir estos datos a través de subconsultas:
(SELECT DISTINCT
W0.[DocDate]
FROM
[dbo].[ORIN] W0 LEFT JOIN
[dbo].[RIN1] W1 ON W0.DocEntry = W1.DocEntry LEFT JOIN
[dbo].[INV1] W2 ON W1.BaseEntry = W2.DocEntry AND W1.BaseLine = W2.LineNum AND W1.BaseType = 13 LEFT JOIN
[dbo].[OINV] W3 ON W2.DocEntry = W3.DocEntry
WHERE W3.[DocEntry] = T6.[DocEntry]) AS 'Fecha Nota Crédito',
(SELECT DISTINCT
X0.[DocNum]
FROM
[dbo].[ORIN] X0 LEFT JOIN
[dbo].[RIN1] X1 ON X0.DocEntry = X1.DocEntry LEFT JOIN
[dbo].[INV1] X2 ON X1.BaseEntry = X2.DocEntry AND X1.BaseLine = X2.LineNum AND X1.BaseType = 13 LEFT JOIN
[dbo].[OINV] X3 ON X2.DocEntry = X3.DocEntry
WHERE X3.[DocEntry] = T6.[DocEntry]) AS 'Número Nota Crédito',
(SELECT DISTINCT
Y0.[BaseAmnt]
FROM
[dbo].[ORIN] Y0 LEFT JOIN
[dbo].[RIN1] Y1 ON Y0.DocEntry = Y1.DocEntry LEFT JOIN
[dbo].[INV1] Y2 ON Y1.BaseEntry = Y2.DocEntry AND Y1.BaseLine = Y2.LineNum AND Y1.BaseType = 13 LEFT JOIN
[dbo].[OINV] Y3 ON Y2.DocEntry = Y3.DocEntry
WHERE Y3.[DocEntry] = T6.[DocEntry]) AS 'Importe Base NC',
(SELECT DISTINCT
Z0.[DocTotal]
FROM
[dbo].[ORIN] Z0 LEFT JOIN
[dbo].[RIN1] Z1 ON Z0.DocEntry = Z1.DocEntry LEFT JOIN
[dbo].[INV1] Z2 ON Z1.BaseEntry = Z2.DocEntry AND Y1.BaseLine = Z2.LineNum AND Z1.BaseType = 13 LEFT JOIN
[dbo].[OINV] Z3 ON Z2.DocEntry = Z3.DocEntry
WHERE Z3.[DocEntry] = T6.[DocEntry]) AS 'Total Nota Crédito',
(Quizás sean subconsultas muy largas, no tengo mucho conocimiento en SQL por lo que aún no se me ocurre una forma de optimizar toda la consulta)
El caso es que cuando agrego estas subconsultas al query principal, me arroja este error:
1). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 'Documento' (RDOC)
No he encontrado la causa de este error, por lo que recurro a su ayuda para resolver este problema.
Un saludo y muchas gracias.