Path: news.easynews.com!easynews!news.he.net!newsfeed.direct.ca!look.ca!newshub2.rdc1.sfba.home.com!news.home.com!news1.sttln1.wa.home.com.POSTED!not-for-mail
Reply-To: "Jim Kennedy" <kennedy-family@home.com>
From: "Jim Kennedy" <kennedy-family@home.com>
Newsgroups: comp.databases.oracle.server
References: <9d90ea9a.0111280658.57a631c4@posting.google.com>
Subject: Re: To convert code from SQL Server.
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
Message-ID: <%H8N7.89028$XJ4.48766279@news1.sttln1.wa.home.com>
Date: Wed, 28 Nov 2001 16:57:31 GMT
NNTP-Posting-Host: 65.4.45.60
X-Complaints-To: abuse@home.net
X-Trace: news1.sttln1.wa.home.com 1006966651 65.4.45.60 (Wed, 28 Nov 2001 08:57:31 PST)
NNTP-Posting-Date: Wed, 28 Nov 2001 08:57:31 PST
Organization: Excite@Home - The Leader in Broadband http://home.com/faster
Xref: easynews comp.databases.oracle.server:126529
X-Received-Date: Wed, 28 Nov 2001 15:27:27 MST (news.easynews.com)

It isn't a stored procedure.  It is a DML statement.  That's why it won't
compile.  Also you should always include the Oracle version.
Jim
"Manish" <manish1000@hotmail.com> wrote in message
news:9d90ea9a.0111280658.57a631c4@posting.google.com...
> Dear oracle experts....
>
> This statement works from the SQL Server, however could not be
> compiled as the stored procedure in Oracle, could someone please help
> me with this?
>
> SQL server original statement was:
>
> update TS_TRANSOFFERING_Temp
> set pricedataid=A1.pricedataid
> from TS_TRANSOFFERING_Temp A, TS_TRANSOFFERING_Temp02 A1
> where A1.start_time=A.start_time
> and A1.stop_time=A.stop_time
> and RTRIM(A1.path_name)=RTRIM(A.path_name)
> and RTRIM(A1.service_increment)=RTRIM(A.service_increment)
> and RTRIM(A1.ts_class)=RTRIM(A.ts_class)
> and RTRIM(A1.ts_type)=RTRIM(A.ts_type)
> and RTRIM(A1.ts_period)=RTRIM(A.ts_period)
> and RTRIM(A1.ts_window)=RTRIM(A.ts_window)
> and isnull(RTRIM(A1.ts_subclass),'')=isnull(RTRIM(A.ts_subclass),'')
>
> Oracle translation:
>
> update TS_TRANSOFFERING_TEMP A set pricedataid = nvl((select
> min(A1.pricedataid)
> from TS_TRANSOFFERING_TEMP02 A1
> where A1.start_time = A.start_time
>    and A1.stop_time = A.stop_time
> and RTRIM(A1.path_name) = RTRIM(A.path_name)
> and RTRIM(A1.service_increment) = RTRIM(A.service_increment)
> and RTRIM(A1.ts_class) = RTRIM(A.ts_class)
> and RTRIM(A1.ts_type) = RTRIM(A.ts_type)
> and RTRIM(A1.ts_period) = RTRIM(A.ts_period)
> and RTRIM(A1.ts_window) = RTRIM(A.ts_window)
> and NVL(RTRIM(A1.ts_subclass),'NULL') =
> NVL(RTRIM(A.ts_subclass),'NULL')), A.pricedataid)
>
> Thank you,
>
> Manish