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.

So any and all help would be greatly appreciated.

Also, there's nothing secret about this algorithm, it's pretty
standard. When it's done I intend to publish it in my blog so other MS
SQL folks will better understand how to make Stored Procedures in
ElevateDB.

Perhaps Tim will even include it in his manual.

Thank you all and let's have some fun


ALTER PROCEDURE [dbo].[spAmortizationSchedule]

----------------------------------------------------------------
-- INPUT PARAMETERS WITH DEFAULT VALUES
----------------------------------------------------------------
@StartDate  datetime      = '2011-09-15' ,
@Principal  decimal(19,2) = 195000       ,
@APR        decimal(19,4) = 3.25         ,
@Months     integer       = 360

AS

----------------------------------------------------------------
-- VARIABLE DECLARATIONS USED FOR PROCESSING
----------------------------------------------------------------
DECLARE @Payment      decimal(19,2)
DECLARE @PaymentLast  decimal(19,2)
DECLARE @PmtNumber    int
DECLARE @PmtDate      date
DECLARE @BalanceStart decimal(19,2)
DECLARE @PmtInterest  decimal(19,2)
DECLARE @PmtPrincipal decimal(19,2)
DECLARE @BalanceEnd   decimal(19,2)

----------------------------------------------------------------
-- TEMP TABLE TO HOLD AMORTIZATION OUTPUT
----------------------------------------------------------------
CREATE TABLE #TempAmortization
(
PmtNumber    int           ,
PmtDate      date          ,
PmtAmount    decimal(19,2) ,
BalanceStart decimal(19,2) ,
PmtPrincipal decimal(19,2) ,
PmtInterest  decimal(19,2) ,
BalanceEnd   decimal(19,2)
)

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

----------------------------------------------------------------
-- 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
BEGIN
 
 SET @PmtNumber    = @PmtNumber +1
 SET @BalanceStart = @BalanceEnd
 SET @PmtDate      = DATEADD(m, @PmtNumber-1, @StartDate)
 SET @PmtInterest  = ROUND(@BalanceStart *(@APR/1200) ,2)
 SET @PmtPrincipal = @Payment - @PmtInterest
 SET @BalanceEnd   = @BalanceStart - @PmtPrincipal

 INSERT INTO #TempAmortization
 (
 PmtNumber     ,
 PmtDate       ,
 PmtAmount     ,
 BalanceStart  ,
 PmtPrincipal  ,
 PmtInterest   ,
 BalanceEnd    
 )
 VALUES
 (
 @PmtNumber     ,
 @PmtDate       ,  
 @Payment       ,
 @BalanceStart  ,
 @PmtPrincipal  ,
 @PmtInterest   ,
 @BalanceEnd    
 )
 
END

----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR THE LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
----------------------------------------------------------------
SET @PmtNumber    = @PmtNumber +1
SET @BalanceStart = @BalanceEnd
SET @PmtDate      = DATEADD(m, @PmtNumber-1, @StartDate )
SET @PmtInterest  = ROUND(@BalanceStart *(@APR/1200) ,2)
SET @PaymentLast  = @BalanceStart + @PmtInterest
SET @PmtPrincipal = @BalanceStart
SET @BalanceEnd   = @BalanceStart + @PmtInterest - @PaymentLast

INSERT INTO #TempAmortization
(
PmtNumber     ,
PmtDate       ,
PmtAmount     ,
BalanceStart  ,
PmtPrincipal  ,
PmtInterest   ,
BalanceEnd    
)
VALUES
(
@PmtNumber     ,
@PmtDate       ,  
@PaymentLast   ,
@BalanceStart  ,
@PmtPrincipal  ,
@PmtInterest   ,
@BalanceEnd    
)

----------------------------------------------------------------
-- RETURN RESULTS FROM TEMPORARY TABLE
----------------------------------------------------------------
SELECT  
PmtNumber     ,
PmtDate       ,
PmtAmount     ,
BalanceStart  ,
PmtPrincipal  ,
PmtInterest   ,
BalanceEnd    
FROM #TempAmortization

----------------------------------------------------------------
-- HOUSEKEEPING
----------------------------------------------------------------
DROP TABLE #TempAmortization







Michael Riley
GySgt USMC Retired
www.zilchworks.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.

What are the results of you own attempts to "translate" the procedure?
Where exactly have you problems to find the right syntax?

Regards Uli

Uli,

>>
What are the results of you own attempts to "translate" the
procedure? Where exactly have you problems to find the right syntax?
<<

I get the following... The procedure was executed successfuly in 0
seconds. There is no output... the table is empty.

I will try again after dinner  


Michael Riley
GySgt USMC Retired
www.zilchworks.com

Uli,

>>
What are the results of you own attempts to "translate" the
procedure? Where exactly have you problems to find the right syntax?
<<

I believe most of my problems stem from the fact that my input
parameters are NULL! ;*(

There were there yesterday... I assumed they were still there today.
Wrong! >:D

Back to the drawing board.


Michael Riley
GySgt USMC Retired
www.zilchworks.com

I finally converted my MS Sql Stored Procedure over to ElevateDB. It
should have been much easier than I made it all because I did not
notice NULL input values. That was such a colossal waiste of time.

I learned quite a bit I must say. I got a strange error trying to input
the PaymentDate which  I set during each iteration within the loop
using:

SET PmtDate = PmtDate + INTERVAL '1' MONTH;

I got the following error:

ElevateDB Error #700 An error was found in the statement at line 58 and
column 21 (Expected NULL or Date expression but instead found
'2011-09-15')

I'm assuming it's during the EXECUTE IMMEDIATE statement.

 EXECUTE IMMEDIATE '
 INSERT INTO TempAmortization
 (
 PmtNumber     ,
--  PmtDate       ,
 PmtAmount     ,
 BalanceStart  ,
 PmtPrincipal  ,
 PmtInterest   ,
 BalanceEnd    
 )
 VALUES
 (
 '   + CAST(PmtNumber    as varchar(25)) + '   ,
--  ''' + CAST(PmtDate      as varchar(25)) + ''' ,
 '   + CAST(Payment      as varchar(25)) + '   ,
 '   + CAST(BalanceStart as varchar(25)) + '   ,
 '   + CAST(PmtPrincipal as varchar(25)) + '   ,
 '   + CAST(PmtInterest  as varchar(25)) + '   ,
 '   + CAST(BalanceEnd   as varchar(25)) + '
 )
 ';

So I just commented it out for now. I'll look into when I have more
time. Been a long day.


Michael Riley
GySgt USMC Retired
www.zilchworks.com

Michael


Err why are you casting things to varchar?

Roy Lambert [Team Elevate]

Michael

> I got the following error:
>
> ElevateDB Error #700 An error was found in the statement at line 58 and
> column 21 (Expected NULL or Date expression but instead found
> '2011-09-15')
>

When using a date literal you need to qualify it ie date'2011-09-15' instead of just '2011-09-15'

John

Roy,

>>
Err why are you casting things to varchar?
<<

Becuase everything else I tried didn't work.


Michael Riley
GySgt USMC Retired
www.zilchworks.com

John,

>>
-->ElevateDB Error #700 An error was found in the statement at line
-->58 and  column 21 (Expected NULL or Date expression but instead
-->found  '2011-09-15')


When using a date literal you need to qualify it ie date'2011-09-15'
instead of just '2011-09-15'
<<

I don't understand what you mean. If you could post a piece of sample
code that would be extrremely helpful.


Michael Riley
GySgt USMC Retired
www.zilchworks.com

John


I think its because, some some reason, Michael's casting all of the input values into VarChars. Looking back at the MS SQL example he posted and from SET PmtDate = PmtDate + INTERVAL '1' MONTH; in his post they are already the right type.

Roy Lambert