Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 26 total |
Help Converting MS SQL Stored Procedure To ElevateDB |
Mon, Sep 5 2011 3:53 PM | Permanent Link |
Michael Riley ZilchWorks | 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 |
Mon, Sep 5 2011 4:46 PM | Permanent Link |
Uli Becker | 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 |
Mon, Sep 5 2011 8:06 PM | Permanent Link |
Michael Riley ZilchWorks | 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 |
Mon, Sep 5 2011 9:46 PM | Permanent Link |
Michael Riley ZilchWorks | 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 |
Mon, Sep 5 2011 11:07 PM | Permanent Link |
Michael Riley ZilchWorks | 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 |
Tue, Sep 6 2011 4:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Michael
Err why are you casting things to varchar? Roy Lambert [Team Elevate] |
Tue, Sep 6 2011 6:26 AM | Permanent Link |
John Hay | 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 |
Tue, Sep 6 2011 7:23 AM | Permanent Link |
Michael Riley ZilchWorks | 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 |
Tue, Sep 6 2011 7:28 AM | Permanent Link |
Michael Riley ZilchWorks | 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 |
Tue, Sep 6 2011 7:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Friday, November 1, 2024 at 07:01 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |