1 Reply Latest reply: Apr 28, 2011 10:53 PM by Felipe Loyola RSS

Ayuda con Query

Isabel Solis Corbala
Currently Being Moderated

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!

  • Re: Ayuda con Query
    Felipe Loyola
    Currently Being Moderated

    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

Actions