Re: Covert Oracle SQL to MSSQL statement?

From: Bent Stigsen <ngcdo_at_thevoid.dk>
Date: Wed, 01 Sep 2004 00:11:03 +0200
Message-ID: <4134f778$0$196$edfadb0f@dread14.news.tele.dk>

Philip Mette wrote:
> I am in a crunch and need to covert this Oracle statement to MSSQL. Is
> there any Oracle/MSSQL experts out there that can help me? I do not
> understand the syntax enough to modify this.

I assume you have some experience with MSSQL's stored procedures.

> Thanks so much for any assistance. Here is the procedure.
>
>
> CREATE PROCEDURE UPD_ACTIVITY IS
> CURSOR ACT_cur1
> IS
> SELECT DISTINCT A.ACCT_NUM, A.DUE_DATE
> FROM TBLCACSDAILYACTIVITIES A ; /* this picks the unique
> combination of acct and date*/

declare ACT_cur1 cursor local for

        SELECT  DISTINCT A.ACCT_NUM, A.DUE_DATE
        FROM TBLCACSDAILYACTIVITIES A



> 	  tot_pay number ;

> 	  rec_upd varchar2(1);

> 	  todays_date date;


declare @tot_pay float

declare @rec_upd varchar(1)
declare @todays_date datetime

> BEGIN
> dbms_output.put_line('inside upd_activity ');

print 'inside upd_activity '

> SELECT SYSDATE INTO todays_date FROM DUAL;

set @todays_date = (select getdate())

> FOR ACT_CUR_REC1 IN ACT_CUR1
> LOOP
No exact equivalence in MSSQL, you would do something like

open ACT_cur1
while (1=1)
begin

   fetch next

     from ACT_cur1
     into @C1_ACCT_NUM, @C1_DUE_DATE  /*declare first*/

   if (@@fetch_status <> 0) break

> rec_upd := 'N';

set @rec_upd = 'N'

> DECLARE
> CURSOR ACT_CUR2 IS
> SELECT B.ACCT_NUM,
> B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,
> B.TOTAL_DOLLARS_COLL
> FROM TBLCACSDAILYACTIVITIES B
> WHERE B.ACCT_NUM = ACT_CUR_REC1.ACCT_NUM
> AND B.DUE_DATE = ACT_CUR_REC1.DUE_DATE
> ORDER BY B.ACTIVITY_DATE
> FOR UPDATE OF B.PROMISE_STATUS,B.TOTAL_DOLLARS_COLL
> NOWAIT;
Dont know the equivalence for NOWAIT, try without. besides that, declare as the previous cursor

> BEGIN
> dbms_output.put_line('inside upd_activity1111 ');
> FOR ACT_CUR_REC2 IN ACT_CUR2
> LOOP
> BEGIN
same as before

> SELECT SUM(C.PAY_AMT) into tot_pay
> FROM TBLCACSDAILYPAYMENTS C
> WHERE C.ACCT_NUM = ACT_CUR_REC2.ACCT_NUM
> AND C.ACTIVITY_DATE >= ACT_CUR_REC2.ACTIVITY_DATE
> AND C.ACTIVITY_DATE < ACT_CUR_REC2.ACTIVITY_DATE+10;

set @tot_pay = (

         SELECT SUM(C.PAY_AMT)
         FROM TBLCACSDAILYPAYMENTS C
         WHERE  C.ACCT_NUM = @C2_ACCT_NUM         /*NB var*/
         AND C.ACTIVITY_DATE >= @C2_ACTIVITY_DATE
         AND C.ACTIVITY_DATE < @C2_ACTIVITY_DATE+10
     )



> 	   		  dbms_output.put_line('tot_pay =');

> 		  	  IF tot_pay >= .9 *  ACT_CUR_REC2.PROMISE_AMT_1  THEN

if (tot_pay >= .9 * @PROMISE_AMT_1)
begin

> IF rec_upd = 'N' THEN /* recs have not bee updated for promise
> status*/
> UPDATE TBLCACSDAILYACTIVITIES
> SET PROMISE_STATUS = 'PK',
> TOTAL_DOLLARS_COLL = tot_pay
> WHERE CURRENT OF ACT_CUR2;
> rec_upd := 'Y';
> ELSIF rec_upd = 'Y' THEN

end
else if (@rec_upd = 'Y')
begin

> UPDATE TBLCACSDAILYACTIVITIES
> SET PROMISE_STATUS = 'IP'
> WHERE CURRENT OF ACT_CUR2;
> END IF;
> ELSIF tot_pay < .9 * ACT_CUR_REC2.PROMISE_AMT_1 THEN
> IF (ACT_CUR_REC2.ACTIVITY_DATE+10) > todays_date THEN
> UPDATE TBLCACSDAILYACTIVITIES
> SET PROMISE_STATUS = 'OP'
> WHERE CURRENT OF ACT_CUR2;
> BREAK;
> ELSE
> UPDATE TBLCACSDAILYACTIVITIES
> SET PROMISE_STATUS = 'PB'
> WHERE CURRENT OF ACT_CUR2;
> END IF;
> END IF;
> END;
> END LOOP; /* end of ACT_CUR_REC2 loop */
> END;
> END LOOP; /*end of ACT_CUR_REC1 */
> END ; /* end of proc */
> /
Received on Tue Aug 31 2004 - 17:11:03 CDT