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 TBLCACSDAILYACT IVITIES 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_STA TUS,
B.TOTAL_DOLLARS _COLL
FROM TBLCACSDAILYACT IVITIES B
WHERE B.ACCT_NUM = ACT_CUR_REC1.AC CT_NUM
AND B.DUE_DATE = ACT_CUR_REC1.DU E_DATE
ORDER BY B.ACTIVITY_DATE
FOR UPDATE OF B.PROMISE_STATU S,B.TOTAL_DOLLA RS_COLL
NOWAIT;
BEGIN
dbms_output.put _line('inside upd_activity111 1 ');
FOR ACT_CUR_REC2 IN ACT_CUR2
LOOP
BEGIN
SELECT SUM(C.PAY_AMT) into tot_pay
FROM TBLCACSDAILYPAY MENTS C
WHERE C.ACCT_NUM = ACT_CUR_REC2.AC CT_NUM
AND C.ACTIVITY_DATE >= ACT_CUR_REC2.AC TIVITY_DATE
AND C.ACTIVITY_DATE < ACT_CUR_REC2.AC TIVITY_DATE+10;
dbms_output.put _line('tot_pay =');
IF tot_pay >= .9 * ACT_CUR_REC2.PR OMISE_AMT_1 THEN
IF rec_upd = 'N' THEN /* recs have not bee updated for promise
status*/
UPDATE TBLCACSDAILYACT IVITIES
SET PROMISE_STATUS = 'PK',
TOTAL_DOLLARS_C OLL = tot_pay
WHERE CURRENT OF ACT_CUR2;
rec_upd := 'Y';
ELSIF rec_upd = 'Y' THEN
UPDATE TBLCACSDAILYACT IVITIES
SET PROMISE_STATUS = 'IP'
WHERE CURRENT OF ACT_CUR2;
END IF;
ELSIF tot_pay < .9 * ACT_CUR_REC2.PR OMISE_AMT_1 THEN
IF (ACT_CUR_REC2.A CTIVITY_DATE+10 ) > todays_date THEN
UPDATE TBLCACSDAILYACT IVITIES
SET PROMISE_STATUS = 'OP'
WHERE CURRENT OF ACT_CUR2;
BREAK;
ELSE
UPDATE TBLCACSDAILYACT IVITIES
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 */
/ 1 7034
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 TBLCACSDAILYACT IVITIES 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 TBLCACSDAILYACT IVITIES 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_STA TUS, B.TOTAL_DOLLARS _COLL FROM TBLCACSDAILYACT IVITIES B WHERE B.ACCT_NUM = ACT_CUR_REC1.AC CT_NUM AND B.DUE_DATE = ACT_CUR_REC1.DU E_DATE ORDER BY B.ACTIVITY_DATE FOR UPDATE OF B.PROMISE_STATU S,B.TOTAL_DOLLA RS_COLL NOWAIT;
Dont know the equivalence for NOWAIT, try without.
besides that, declare as the previous cursor
BEGIN dbms_output.put _line('inside upd_activity111 1 '); FOR ACT_CUR_REC2 IN ACT_CUR2 LOOP BEGIN
same as before
SELECT SUM(C.PAY_AMT) into tot_pay FROM TBLCACSDAILYPAY MENTS C WHERE C.ACCT_NUM = ACT_CUR_REC2.AC CT_NUM AND C.ACTIVITY_DATE >= ACT_CUR_REC2.AC TIVITY_DATE AND C.ACTIVITY_DATE < ACT_CUR_REC2.AC TIVITY_DATE+10;
set @tot_pay = (
SELECT SUM(C.PAY_AMT)
FROM TBLCACSDAILYPAY MENTS C
WHERE C.ACCT_NUM = @C2_ACCT_NUM /*NB var*/
AND C.ACTIVITY_DATE >= @C2_ACTIVITY_DA TE
AND C.ACTIVITY_DATE < @C2_ACTIVITY_DA TE+10
)
dbms_output.put _line('tot_pay ='); IF tot_pay >= .9 * ACT_CUR_REC2.PR OMISE_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 TBLCACSDAILYACT IVITIES SET PROMISE_STATUS = 'PK', TOTAL_DOLLARS_C OLL = tot_pay WHERE CURRENT OF ACT_CUR2; rec_upd := 'Y'; ELSIF rec_upd = 'Y' THEN
end
else if (@rec_upd = 'Y')
begin
UPDATE TBLCACSDAILYACT IVITIES SET PROMISE_STATUS = 'IP' WHERE CURRENT OF ACT_CUR2; END IF; ELSIF tot_pay < .9 * ACT_CUR_REC2.PR OMISE_AMT_1 THEN IF (ACT_CUR_REC2.A CTIVITY_DATE+10 ) > todays_date THEN UPDATE TBLCACSDAILYACT IVITIES SET PROMISE_STATUS = 'OP' WHERE CURRENT OF ACT_CUR2; BREAK; ELSE UPDATE TBLCACSDAILYACT IVITIES 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 */ / This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jeff Lambert |
last post by:
We have a Windows client application written in delphi that connects
to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop
and finally send the strings list to be executed.
What I really need help with are the first three and the last three
statements. How would I rewrite those under Oracle to make sure they
have the same behaviour? BTW I know this isn't the best way to learn
Oracle PLSQL but I am under time constraints,...
|
by: jonathan |
last post by:
hey all,
I realize that this question might pop up from time to time, but I
haven't seen it a while and things might of changed, so -
Right now (July 2004) how does mysql stand up in comparison to oracle?
We are seriously considering migrating our multi-processor oracle
system to mysql to save on licensing costs, and would need several
features that mysql may or may not have:
|
by: English Teacher |
last post by:
Which would be a more useful relational database server to learn
nowadays: MS SQL SERVER or ORACLE?
Thanks!
|
by: Sudhesh Nayak |
last post by:
Hi,
I have an Oracle (8.1) & a SQL Server 2000 database with
Production data. There are situations when I need data from both the
databases. My first choice was to link Oracle to SQL and run DTS
overnight. But this would have a 1 day latency not to mention the time
it would take.
1. Has any one tried real time access via Linked server to Oracle?
How good is the performance?
|
by: James Alexander Starritt |
last post by:
I also posted similarly in mailing.database.mysql
I have created a rather large (60 table database) website dealio in PHP that
works with MS SQL Server, Oracle, MySQL and presumably any other data base I
add to
the wrapper functions that I created. All the development was done in
Oracle, but now its time to create that large datastructure again in
MySQL -- now I've built this all in notepad for Oracle (twice) and built it
in SQL Server...
| |
by: Pardhasaradhy |
last post by:
Hello,
I have a linked server to oracle 7.1 from SQL 2000. when I try to
execute simple select statement which returns one row of data using
openquery is not fetching the data. After 30 minutes SQL Query
analyser is hanging. This is happening on the production server and it
is working from last 6 months. I have tried by deleting and recreating
the linked server, but no use. Please help
Thanks,
|
by: jrefactors |
last post by:
I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures
execute in the database server with better performance?
Please advise good references for Oracle stored procedures also.
thanks!!
|
by: prunoki |
last post by:
Hello,
I am an SQL server newbie. Our company has a massive application
written in PL/SQL. I need to port parts of it to SQL Server.
- Which SQL server version should I choose, to have a reasonable chance
of porting?
- Could you recommend any best practices, tools? I use Toad for SQL
development on Oracle, I am looking for something similar for SQL
|
by: becksinthecity |
last post by:
I'm trying to convert the below SQL into Oracle but am having some issues with the variable declarations.
SQL commands
declare @sqlstring varchar(500)
select @sqlstring= (select concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a )
exec('select * from'+@sqlstring+'')
End SQL
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |