To convert code from SQL Server.

From: Manish <manish1000_at_hotmail.com>
Date: 28 Nov 2001 06:58:02 -0800
Message-ID: <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 Received on Wed Nov 28 2001 - 08:58:02 CST