Hi,

What is wrong with the "RETURN" clause?  If I remove the "IF' clause it works ok but with the "IF" clause I get an error.




ALTER FUNCTION "IsQuantityAvailableInStock" (IN "iYearSysNo" INTEGER, IN "iBranchSysNo" INTEGER, IN "iStoreSysNo" INTEGER, IN "iItemSysNo" INTEGER, IN "sLOTNumber" VARCHAR(20) COLLATE ANSI, IN "dOriginalQuantity" FLOAT, IN "dRequiredQuantity" FLOAT, IN "iOriginalExpirationDate" INTEGER, IN "iExpirationDate" INTEGER)
RETURNS BOOLEAN
BEGIN

DECLARE dQuantity FLOAT DEFAULT 0;
DECLARE Result  BOOLEAN DEFAULT FALSE;
DECLARE qryData CURSOR FOR sSQL;




PREPARE sSQL FROM 'SELECT SUM(I.ItemQuantity * U.UnitFactor) AS dQuantity FROM TB_Items I
                  INNER JOIN TB_Units U ON U.BranchSysNo = I.BranchSysNo AND U.UnitSysNo = I.UnitSysNo
                  WHERE I.YearSysNo   = ?
                  AND   I.BranchSysNo = ?
                  AND   I.StoreSysNo  = ?
                  AND   I.ItemSysNo   = ?';
OPEN qryData USING iYearSysNo,iBranchSysNo,iStoreSysNo,iItemSysNo;

FETCH FIRST FROM qryData('dQuantity') INTO dQuantity;

CLOSE qryData;

IF (dQuantity = 0) THEN
  RETURN FALSE; <<=================I get an error here.
ELSE
  RETURN TRUE;
END;         










END
Regards,
Abdulaziz Jasser

Abdulaziz


Could be your syntax (caught me a few times)

From the OLH

IF <BooleanExpression> THEN <StatementBlock> [ELSEIF <BooleanExpression> THEN <StatementBlock>] [ELSE <StatementBlock>] END IF;

Roy Lambert

Roy,

If I remove the "IF" clause and just put "RETURN FALSE" then it works!!!  The "IF" clause is very simple and the whole function is direct and simple.  This was supposed to be a piece of cake!!!
Regards,
Abdulaziz Jasser

SOLVED...



BEGIN
DECLARE Result    BOOLEAN DEFAULT FALSE;
DECLARE dQuantity FLOAT   DEFAULT 0;



DECLARE qryData CURSOR FOR sSQL;




PREPARE sSQL FROM 'SELECT SUM(I.ItemQuantity * U.UnitFactor) AS dQuantity FROM TB_Items I
                  INNER JOIN TB_Units U ON U.BranchSysNo = I.BranchSysNo AND U.UnitSysNo = I.UnitSysNo
                  WHERE I.YearSysNo   = ?
                  AND   I.BranchSysNo = ?
                  AND   I.StoreSysNo  = ?
                  AND   I.ItemSysNo   = ?';
OPEN qryData USING iYearSysNo,iBranchSysNo,iStoreSysNo,iItemSysNo;

FETCH FIRST FROM qryData('dQuantity') INTO dQuantity;

CLOSE qryData;

IF dQuantity = NULL THEN
  SET dQuantity = 0;
END IF;

IF ((dOriginalQuantity + dRequiredQuantity) < dQuantity) THEN
  SET Result = TRUE;
ELSE
  SET Result = FALSE;
END IF;

RETURN Result;
Regards,
Abdulaziz Jasser

Abdulaziz,

<< What is wrong with the "RETURN" clause?  If I remove the "IF' clause it
works ok but with the "IF" clause I get an error. >>

As you already found out, you can only have one single RETURN clause in any
function.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<If you have any other questions, please let me know.>>

Thanks Tim...I just start using those very useful stored procedures and my only reference is EDB help files.   In the current application it is Fat-Client with Thin-Server and it is very slow over slow internet connections.  So I am moving some of the code to those procedures so my application will be Thin-Client with Fat-Server for better performance.  Therefore, you will probably hear me soon asking more questions.
Regards,
Abdulaziz Jasser