Re: Outer Joins and Select ... for update of statements

From: Jerry Gitomer <jgitomer_at_p3.net>
Date: 1998/02/28
Message-ID: <34F8DFA7.7335@p3.net>#1/1

Hi Stephen,

Okay, I could be wrong, but isn't combining an outer join with an update an oxymoron? I mean, an outer join says create an output consisting of everything in the driven table that matches the driving table and also include everything from the driving table that isn't in the driven table.

If I am not mistaken you are asking Oracle to update rows that don't exist.

If you really want to do this I think you will have to insert the "missing" rows into your table and then just do an update without an outer join.

Regards

Jerry         

        Stephen Walsh wrote:          

         In Oracle 7.3 on NT and HPUX 10, the following select statement is part

	 of a database procedure. When the procedure is executed, the database
	 procedure hangs when this statement is reached:
	 
	 SELECT  lt.ROWID,
	                 lt.job_no,
	                 TO_CHAR(lt.sequence_no),
	                 lt.cost,
	                 lt.item_used_date,
	                 lt.posting_code,
	                 fi.order_no
	 FROM    ledger_tfr lt,
	                 res_expense_element re,
	                 facility_extra_data fi                 /* outer join
*/
	 WHERE   lt.facility_code            = fi.facility_code(+)
	 AND     re.resource_code          = lt.resource_code
	 AND     re.gl_ctrl_account          =  '1234'
	 AND     lt.gl_batch_id                 = '000011'
	 AND     SUBSTR(lt.job_no,1,1)   = 'J'
	 AND    (lt.gl_period =  2 OR lt.projects_period = 2)
	 AND     SUBSTR(lt.posting_code,1,4) = '0101'
	 FOR UPDATE OF lt.job_no;
	 
	 It was narrowed down to the combination of the outer join and FOR
UPDATE
	 OF lt.job_no.
	 
	 Without the FOR UPDATE statement, the select statement runs as

expected.          

         Does anyone know why an outer join and for update statement combination

         causes a statement to hang.          

	 Any comments would be appreciated.
	 --
	 Regards
	 Stephen Walsh


	 ===========================================

	 Stephen Walsh
	 Helix Systems Pty Ltd
	 Ph:  +61 7 3846 1999
	 Fax:  +61 7 3846 2122
	 Email:  stevew_at_helix.com.au
	 Web: http://www.helix.com.au/


	 ===========================================


-- 
Jerry Gitomer		Since I know how to spell DBA I became one. 

jgitomer_at_p3.net Received on Sat Feb 28 1998 - 00:00:00 CST