Hi,
below is some code that gives strange results on MaxDB 7.5.00.19,
maybe somebody can help or tell why this happens. Also, although we
make it works, it sometimes cut the result when with the select it has
to show a lot of results/lines, ordering the result helps this when
the required result/calculation comes at the first lines but sometimes
it fails to do the job in next/last lines.
Hope i have explained it well. Perhaps its a bug with .19 version?
What version is right now best for production?
Thanks,
Josu.
//
// This function works as expected the only thing changed to make it
work is the FROM
// section of the sql
//
CREATE FUNCTION AVG_PRICE (IDP INTEGER) RETURNS FIXED(10,2) AS
VAR suma FIXED(10,2);
price FIXED(10,2);
TRY
SET suma = 0;
SELECT PVP FROM DBA.PRODUCTO AS P , DBA.TARIFAPRODUCTO AS TP WHERE
P.IDPRODUCTO = :idp AND P.IDPRODUCTO=TP.IDPRODUCTO;
WHILE $RC = 0 DO BEGIN
FETCH INTO :price;
SET suma = suma + price;
END;
CATCH
if $rc<>100 THEN STOP ( $rc , 'error en funcion') ;
IF suma > 0 THEN RETURN suma
ELSE RETURN 999;
//
// this function doesnt work, notice the simple FROM with only 1
table. It doesnt seek on
// table to calculate the result.
//
CREATE FUNCTION AVG_PRICE (IDP INTEGER) RETURNS FIXED(10,2) AS
VAR suma FIXED(10,2);
price FIXED(10,2);
TRY
SET suma = 0;
SELECT PVP FROM DBA.TARIFAPRODUCTO WHERE IDPRODUCTO = :idp ;
WHILE $RC = 0 DO BEGIN
FETCH INTO :price;
SET suma = suma + price;
END;
CATCH
if $rc<>100 THEN STOP ( $rc , 'error en funcion') ;
IF suma > 0 THEN RETURN suma
ELSE RETURN 999;
//
// This sql only works with first function, with the second one it
gives nothing as result
//
select producto, idZona, avg_price(idproducto) AS V1 FROM producto
//
// This sql works with both functions when we explicitely enter de in parameter
//
select producto, idZona, avg_price(86) AS V1 FROM producto
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
