Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 21 to 26 of 26 total |
Help Converting MS SQL Stored Procedure To ElevateDB |
Tue, Sep 6 2011 1:53 PM | Permanent Link |
Raul Team Elevate | Michael,
The online docs are actually quite a good reference if you need specific info on syntax or such. I tend to keep a browser tab open almost all the time: Date: http://www.elevatesoft.com/manual?action=contents&id=edb2sql and main sql manual topics http://www.elevatesoft.com/manual?action=contents&id=edb2sql Raul << Thank you. I didn't know i needed the "Date" keyword. Michael Riley >> |
Tue, Sep 6 2011 1:53 PM | Permanent Link |
Raul Team Elevate | Michael,
The online docs are actually quite a good reference if you need specific info on syntax or such. I tend to keep a browser tab open almost all the time: Date: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Date_Time_Types and main sql manual topics http://www.elevatesoft.com/manual?action=contents&id=edb2sql Raul << Thank you. I didn't know i needed the "Date" keyword. Michael Riley >> |
Tue, Sep 6 2011 3:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Michael,
<< I'm new to ElevateDB coming from a Microsoft SQL background. Roy Lambert suggested I whip up an example of a Microsoft Stored procedure that I'd like to get converted over to ElevateDB and post it to the forum. >> I know that you've already got this done, but here's an optimal version for EDB: ALTER PROCEDURE spAmortizationSchedule(INOUT StartDate DATE, INOUT Principal DECIMAL(19,2), INOUT APR DECIMAL(19,4), INOUT Months INTEGER) BEGIN ---------------------------------------------------------------- -- VARIABLE DECLARATIONS USED FOR PROCESSING ---------------------------------------------------------------- DECLARE InsertStmt STATEMENT; DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR ResultStmt; DECLARE Payment DECIMAL(19,2); DECLARE PaymentLast DECIMAL(19,2); DECLARE PmtNumber INTEGER; DECLARE PmtDate DATE; DECLARE BalanceStart DECIMAL(19,2); DECLARE PmtInterest DECIMAL(19,2); DECLARE PmtPrincipal DECIMAL(19,2); DECLARE BalanceEnd DECIMAL(19,2); ---------------------------------------------------------------- -- INPUT PARAMETERS WITH DEFAULT VALUES ---------------------------------------------------------------- SET StartDate = COALESCE(StartDate, CURRENT_DATE()); SET Principal = COALESCE(Principal, 195000); SET APR = COALESCE(APR, 3.25); SET Months = COALESCE(Months, 360); ---------------------------------------------------------------- -- TEMP TABLE TO HOLD AMORTIZATION OUTPUT ---------------------------------------------------------------- -- This seems weird at first, but is required since the ResultStmt -- may still be prepared from the last execution, and we want to -- make sure that the TempAmortization table is not open when we -- attempt to drop it or recreate it. --UNPREPARE ResultStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=?'; OPEN InfoCursor USING 'TempAmortization'; IF ROWCOUNT(InfoCursor) > 0 THEN EXECUTE IMMEDIATE 'EMPTY TABLE TempAmortization'; ELSE EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE TempAmortization ( PmtNumber INTEGER , PmtDate DATE , PmtAmount DECIMAL(19,2) , BalanceStart DECIMAL(19,2) , PmtPrincipal DECIMAL(19,2) , PmtInterest DECIMAL(19,2) , BalanceEnd DECIMAL(19,2) )'; END IF; ---------------------------------------------------------------- -- CALCULATE MONTHLY PAYMENT BASED ON INPUT PARAMETERS -- -- Microsoft SQL LOG -- Microsoft SQL ROUND(<EXPRESSION> , <DECIMALPLACES>) -- ElevateDB SQL LN -- ElevateDB SQL ROUND(<EXPRESSION> to <DECIMALPLACES>) -- -- This line may wrap and be hard to read ---------------------------------------------------------------- SET Payment = ROUND(Principal * ((APR/1200)/(1- EXP((Months*-1) * LOG(1 + (APR/1200))))),2); ---------------------------------------------------------------- -- INITALIZE VARIABLES BEFORE THE LOOP STARTS ---------------------------------------------------------------- SET PmtNumber = 0; SET BalanceEnd = Principal; PREPARE InsertStmt FROM 'INSERT INTO TempAmortization ( PmtNumber , PmtDate , PmtAmount , BalanceStart , PmtPrincipal , PmtInterest , BalanceEnd ) VALUES (?,?,?,?,?,?,?)'; ---------------------------------------------------------------- -- PERFORM CALCULATIONS FOR ALL BUT LAST MONTH AND -- STORE RESULTS IN TEMPORARY TABLE -- -- MICROSOFT SQL DATEADD(<INTERVAL>,<VALUE>,<DATE> -- ELEVATEDB SQL <DATE> + INTERVAL '<VALUE>' <INTERVAL> ---------------------------------------------------------------- WHILE PmtNumber < Months -1 DO SET PmtNumber = PmtNumber + 1; SET BalanceStart = BalanceEnd; SET PmtDate = (StartDate + CAST(PmtNumber-1 AS INTERVAL MONTH)); SET PmtInterest = ROUND(BalanceStart *(APR/1200) ,2); SET PmtPrincipal = Payment - PmtInterest; SET BalanceEnd = BalanceStart - PmtPrincipal; EXECUTE InsertStmt USING PmtNumber , PmtDate , Payment , BalanceStart , PmtPrincipal , PmtInterest , BalanceEnd; END WHILE; ---------------------------------------------------------------- -- PERFORM CALCULATIONS FOR THE LAST MONTH AND -- STORE RESULTS IN TEMPORARY TABLE ---------------------------------------------------------------- SET PmtNumber = PmtNumber +1; SET BalanceStart = BalanceEnd; SET PmtDate = (StartDate + CAST(PmtNumber-1 AS INTERVAL MONTH)); SET PmtInterest = ROUND(BalanceStart *(APR/1200) ,2); SET PaymentLast = BalanceStart + PmtInterest; SET PmtPrincipal = BalanceStart; SET BalanceEnd = BalanceStart + PmtInterest - PaymentLast; EXECUTE InsertStmt USING PmtNumber , PmtDate , PaymentLast , BalanceStart , PmtPrincipal , PmtInterest , BalanceEnd; ---------------------------------------------------------------- -- RETURN RESULTS FROM TEMPORARY TABLE ---------------------------------------------------------------- PREPARE ResultStmt FROM 'SELECT PmtNumber , PmtDate , PmtAmount , BalanceStart , PmtPrincipal , PmtInterest , BalanceEnd FROM TempAmortization'; OPEN ResultCursor; END You'll notice that I also modified the APR parameter so that it uses a scale of 4 instead of 2 - this was to ensure that any calculations involved with the APR didn't get rounded off to only 2 decimal places, which screws up the calculations. EDB determines the scale of any expression through type promotion according to the following rules: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Type_Promotion with the last bit being the relevant bit: If the resulting type is a DECIMAL, then the resulting scale is the greatest of all of the input scales. Also, the TempAmortization table will automatically get dropped when the session is closed, so I just keep it around and empty it each time instead of the create/drop cycle. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Sep 16 2011 4:55 AM | Permanent Link |
Adam Brett Orixa Systems | Fantastic post Tim,
I've copied this procedure away so I have it on file for my own learning when I have to write more complex procedures. I think this whole thread shows that it would be great to have a dedicated space on the forums or EDB Website for people to see examples of EDB SQL. I realise it is "more or less" SQL2003 ... but there are differences & tiny differences often cause the most pain! I am imagining some searchable pages based on a simple table, something like: Author, Name, Description, SQL, DateAdded, EDBSQLVersion, Level (=beginner, intermediate, advanced) I have dozens of my own procedures I would be happy to contribute, all the stuff in the help could be bunged in & I'm sure several other regulars would be happy to dust off & share some of their own efforts. People doing specific things (like MS-SQL->EDB) could demonstrate particular gotchas etc. It could be a new forum on the existing news-groups, or it could be a new page under EDB Technical Support. ... I know its work to set up, but I think it would help a lot of people particularly newbies. |
Mon, Sep 19 2011 12:42 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Just a quick note - this part needs to be removed:
-- This seems weird at first, but is required since the ResultStmt -- may still be prepared from the last execution, and we want to -- make sure that the TempAmortization table is not open when we -- attempt to drop it or recreate it. --UNPREPARE ResultStmt; It's commented out, but its inclusion is confusing because there isn't any DROP TABLE, and instead I went for the EMPTY TABLE option instead. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Sep 19 2011 12:43 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< I think this whole thread shows that it would be great to have a dedicated space on the forums or EDB Website for people to see examples of EDB SQL. I realise it is "more or less" SQL2003 ... but there are differences & tiny differences often cause the most pain! >> I can set up a new newsgroup in a minute. Doing something more would take a bit more time. Any votes on one or the other ? -- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 3 of 3 | |
Jump to Page: 1 2 3 |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |