Ayuda SAP

Query Comisiones

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.

Hola @JeffersonQ09 te sugiero que le agregues uno de los subquery y lo ejecutes, si no te da problema agregas otro… hasta saber cual es el que te da error…

2 Me gusta

Muchas gracias por tu respuesta,
Desde el primer subquery me arroja el error, sin embargo, si coloco un rango de fecha determinado, por ejemplo del 01/02/20 al 28/02/20, me arroja los resultados correctamente, si amplio el rango, me genera error.

Le hice esta modificación y parece funcionar:

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",
	(SELECT MAX(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 MAX(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 MAX(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 MAX(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 Z1.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'
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]

Habría una forma de optimizar esta consulta?

Este tema se cerró automáticamente 30 días después de la última publicación. No se permiten nuevas respuestas.