Board index » off-topic » Aggregate function on Query...

Aggregate function on Query...


2006-01-11 06:38:35 AM
off-topic13
Hi, I have the following query (named ConsCompras):
SELECT C1.Clave AS Clave1, C2.Clave AS Clave2, C3.Clave AS Clave3,
C1.Clasificacion AS Clase1, C2.Clasificacion AS Clase2, C3.Clasificacion
AS Clase3,
SUM(AE.Importe) AS Monto
FROM ArtsEgresos AE INNER JOIN Articulos A ON (AE.CveArticulo = A.Clave),
Clase1 C1, Clase2 C2, Clase3 C3, Egresos E
WHERE (A.Clase1 = C1.Clave)
AND (A.Clase2 = C2.Clave)
AND (A.Clase3 = C3.Clave)
AND (C1.Clave = C2.Clase1)
AND (C1.Clave = C3.Clase1)
AND (C2.Clave = C3.Clase2)
AND (AE.Egreso = E.IdCompra)
AND (E.Fecha BETWEEN :Fecha1 AND :Fecha2)
GROUP BY C1.Clave, C1.Clasificacion, C2.Clave, C2.Clasificacion, C3.Clave,
C3.Clasificacion
ORDER BY C1.Clave, C2.Clave, C3.Clave
And I Excute it with the following code:
ConsCompras.Close;
ConsCompras.UnPrepare;
ConsCompras.ParamByName('Fecha1').AsDate := Fecha1.Date;
ConsCompras.ParamByName('Fecha2').AsDate := Fecha2.Date;
ConsCompras.Prepare;
ConsCompras.Open;
RepEgresos.ExecuteReport('REgresosClase')
Problem is that the report is giving me different results (SUM) when I
execute the report from one execution to another.
How is this possible?
Any clue?
Gabriel.
P.S. I am using D7 with Rave reports and paradox tables on a networked
drive.
 
 

Re:Aggregate function on Query...

Can you create this query in the Paradox environment, to compare
the sum variance you describe?
If the query is working, perhaps another process is locking data from
your query sources. Hence the summing mystery.
The query looks pretty good. Perhaps making a query which performs
your comparisons on a single column may hold some answers.
Sometimes eliminating pieces lead you to the answer.
"Gman" < XXXX@XXXXX.COM >wrote in message
Quote
Hi, I have the following query (named ConsCompras):

SELECT C1.Clave AS Clave1, C2.Clave AS Clave2, C3.Clave AS Clave3,
C1.Clasificacion AS Clase1, C2.Clasificacion AS Clase2, C3.Clasificacion
AS Clase3,
SUM(AE.Importe) AS Monto
FROM ArtsEgresos AE INNER JOIN Articulos A ON (AE.CveArticulo = A.Clave),
Clase1 C1, Clase2 C2, Clase3 C3, Egresos E
WHERE (A.Clase1 = C1.Clave)
AND (A.Clase2 = C2.Clave)
AND (A.Clase3 = C3.Clave)
AND (C1.Clave = C2.Clase1)
AND (C1.Clave = C3.Clase1)
AND (C2.Clave = C3.Clase2)
AND (AE.Egreso = E.IdCompra)
AND (E.Fecha BETWEEN :Fecha1 AND :Fecha2)
GROUP BY C1.Clave, C1.Clasificacion, C2.Clave, C2.Clasificacion, C3.Clave,
C3.Clasificacion
ORDER BY C1.Clave, C2.Clave, C3.Clave

And I Excute it with the following code:

ConsCompras.Close;
ConsCompras.UnPrepare;
ConsCompras.ParamByName('Fecha1').AsDate := Fecha1.Date;
ConsCompras.ParamByName('Fecha2').AsDate := Fecha2.Date;
ConsCompras.Prepare;
ConsCompras.Open;
RepEgresos.ExecuteReport('REgresosClase')

Problem is that the report is giving me different results (SUM) when I
execute the report from one execution to another.

How is this possible?
Any clue?

Gabriel.

P.S. I am using D7 with Rave reports and paradox tables on a networked
drive.