[AccessD] Oracle Pass Thru

By the bye, when the first query finished, even through there were only 139 rows in the table being update, Access said that 8089 rows were going to be updated. I have often run into this with update queries.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Benson, William (GE Global Research, consultant)
Sent: Tuesday, June 12, 2012 1:06 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Oracle Pass Thru

I am a little ashamed at asking so many Oracle query syntax questions, I hope to be out of the woods soon. I would like either to make the non-pass thru query run faster or solve the syntax problem with the pass thru.


Non-pass-thru:
The following query, with linked tables Solarconnect_SID_consolidated_gib_view  and SOLARCONNECT_GIBIX_CUSTOMER_HIERARCHY seems to run, but it takes a long time. Presumably because they are odbc linked tables, but possibly also by poor query construction.



UPDATE Solarconnect_SID_consolidated_gib_view AS A INNER JOIN 
	(SELECT
		 SOLARCONNECT_SID_CONSOLIDATED_GIB_VIEW.SITE_DUNS_NO, 
		DOM.PARENT_CUSTOMER_DUNS
	FROM 	
		SOLARCONNECT_SID_CONSOLIDATED_GIB_VIEW 
	INNER JOIN 
		SOLARCONNECT_GIBIX_CUSTOMER_HIERARCHY AS DOM 
	ON 
		SOLARCONNECT_SID_CONSOLIDATED_GIB_VIEW.SITE_DUNS_NO = DOM.CHILD_CUSTOMER_DUNS
	WHERE 
			DOM.PARENT_CUSTOMER_TYPE="DOM" 
		AND 
			DOM.CHILD_CUSTOMER_TYPE="STE"
	)  AS B 

ON 

	A.Site_duns_no = B.Site_duns_no 
SET	 A.dom_customer_duns_no = B.parent_customer_duns;


I tried making it a pass thru, but then the syntax was rejected (note that when a passthru, the SOLARCONNECT prefix is not needed, that is only for linked tables):


UPDATE SID_consolidated_gib_view A
INNER JOIN
(
SELECT X.SITE_DUNS_NO, DOM.PARENT_CUSTOMER_DUNS FROM SID_CONSOLIDATED_GIB_VIEW X LEFT OUTER JOIN GIBIX_CUSTOMER_HIERARCHY DOM ON X.SITE_DUNS_NO = DOM.CHILD_CUSTOMER_DUNS WHERE DOM.CHILD_CUSTOMER_TYPE='STE'
AND
DOM.PARENT_CUSTOMER_TYPE='DOM'

)  B 



ON A.Site_duns_no = B.Site_duns_no 

SET A.dom_customer_duns_no = B.parent_customer_duns;

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com