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
>>

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
>>

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

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.

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

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