cancel
Showing results for 
Search instead for 
Did you mean: 

Ayuda con Query

Former Member
0 Kudos

Colegas tengo el siguiente query:

declare @ventas numeric(9,2);

Set @ventas=(SELECT SUM(T3.[LineTotal])

FROM

[dbo].[ORCT] T0 INNER JOIN

[dbo].[RCT2] T1 ON T0.DocNum = T1.DocNum INNER JOIN

[dbo].[OINV] T2 ON T1.DocEntry = T2.DocEntry INNER JOIN

[dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry INNER JOIN

[dbo].[OSLP] T4 ON T3.SlpCode = T4.SlpCode INNER JOIN

[dbo].[PCH1] T5 ON T3.ItemCode = T5.ItemCode INNER JOIN

[dbo].[@COMISIONES] T6 ON T3.U_Comision = T6.Code

WHERE T0.[DocDate] BETWEEN '[%0]' AND '[%1]' AND T2.[Project] = '[%2]');

Select @ventas as Ventas

FROM

[dbo].[ORCT] T0 INNER JOIN

[dbo].[RCT2] T1 ON T0.DocNum = T1.DocNum INNER JOIN

[dbo].[OINV] T2 ON T1.DocEntry = T2.DocEntry INNER JOIN

[dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry INNER JOIN

[dbo].[OSLP] T4 ON T3.SlpCode = T4.SlpCode INNER JOIN

[dbo].[PCH1] T5 ON T3.ItemCode = T5.ItemCode INNER JOIN

[dbo].[@COMISIONES] T6 ON T3.U_Comision = T6.Code

WHERE T0.[DocDate] BETWEEN '[%0]' AND '[%1]' AND T2.[Project] = '[%2]'

y me marca un error:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Arithmetic overflow error converting numeric to data type numeric.

Alguna sugerencia???

Saludos!

Accepted Solutions (0)

Answers (1)

Answers (1)

felipe_loyolarodriguez
Active Contributor
0 Kudos

Antepone esto antes de todo el query


/*SELECT * FROM ORCT X0 WHERE X0.DocDate BETWEEN '[%0]' AND '[%1]'*/
DECLARE @fechaini DATETIME
SET @fechaini = '[%0]'
DECLARE @fechafin DATETIME
SET @fechafin = '[%1]'

/*SELECT * FROM OINV X1 WHERE X1.Project = '[%2]' */
DECLARE @Project Nvarchar(8)
SET @project = '[%2]'

Debajo de eso, coloca el query...

Si no jala asi

Pruebalo de esta otra manera


/*SELECT * FROM ORCT X0 WHERE X0.DocDate BETWEEN '[%0]' AND '[%1]'*/
DECLARE @fechaini DATETIME
SET @fechaini = '[%0]'
DECLARE @fechafin DATETIME
SET @fechafin = '[%1]'

/*SELECT * FROM OINV X1 WHERE X1.Project = '[%2]' */
DECLARE @Project Nvarchar(8)
SET @project = '[%2]'

DECLARE @ventas NUMERIC(9,2);
SET @ventas=(SELECT SUM(T3.LineTotal)
FROM 
[dbo].[ORCT] T0 INNER JOIN 
[dbo].[RCT2] T1 ON T0.DocNum = T1.DocNum INNER JOIN 
[dbo].[OINV] T2 ON T1.DocEntry = T2.DocEntry INNER JOIN 
[dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry INNER JOIN 
[dbo].[OSLP] T4 ON T3.SlpCode = T4.SlpCode INNER JOIN 
[dbo].[PCH1] T5 ON T3.ItemCode = T5.ItemCode INNER JOIN 
[dbo].[@COMISIONES] T6 ON T3.U_Comision = T6.Code 
WHERE T0.DocDate BETWEEN @fechaini AND @fechafin AND T2.Project = @Project);

SELECT @ventas as Ventas
FROM 
[dbo].[ORCT] T0 INNER JOIN 
[dbo].[RCT2] T1 ON T0.DocNum = T1.DocNum INNER JOIN 
[dbo].[OINV] T2 ON T1.DocEntry = T2.DocEntry INNER JOIN 
[dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry INNER JOIN 
[dbo].[OSLP] T4 ON T3.SlpCode = T4.SlpCode INNER JOIN 
[dbo].[PCH1] T5 ON T3.ItemCode = T5.ItemCode INNER JOIN 
[dbo].[@COMISIONES] T6 ON T3.U_Comision = T6.Code 
WHERE T0.DocDate BETWEEN @fechaini AND @fechafin AND T2.Project = @Project

Ojala funcione!

Slds

Edited by: Floyola on Apr 28, 2011 1:43 PM