Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 6 of 6 total |
Error in my first EDB stored function. |
Mon, Jul 30 2012 10:35 AM | Permanent Link |
Abdulaziz Al-Jasser | 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 |
Mon, Jul 30 2012 10:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Jul 30 2012 12:23 PM | Permanent Link |
Abdulaziz Al-Jasser | 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 |
Mon, Jul 30 2012 4:41 PM | Permanent Link |
Abdulaziz Al-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 |
Thu, Aug 2 2012 3:10 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Aug 2 2012 3:51 PM | Permanent Link |
Abdulaziz Al-Jasser | 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 |
This web page was last updated on Monday, May 6, 2024 at 03:23 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |