Covert Oracle SQL to MSSQL statement?

From: Philip Mette <philipdm_at_msn.com>
Date: 27 Aug 2004 07:05:59 -0700
Message-ID: <96a56e66.0408270605.60da0069_at_posting.google.com>

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. 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*/

	  tot_pay number ;
	  rec_upd varchar2(1);
	  todays_date date;

BEGIN
   dbms_output.put_line('inside upd_activity ');    SELECT SYSDATE INTO todays_date FROM DUAL;    FOR ACT_CUR_REC1 IN ACT_CUR1
   LOOP

       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;
   	   BEGIN
		  dbms_output.put_line('inside upd_activity1111 ');
		  FOR ACT_CUR_REC2 IN ACT_CUR2
		  LOOP
		  BEGIN
		  	   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;
	   		  dbms_output.put_line('tot_pay =');
		  	  IF tot_pay >= .9 *  ACT_CUR_REC2.PROMISE_AMT_1  THEN
			  	 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
			  	    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 Fri Aug 27 2004 - 16:05:59 CEST