In what way is it not working ? Are you getting an error message, or not getting the results you expect.
In the latter case, this may be because you are cancelling the outer join by not echoing it through all the necessary points in the WHERE clause. e.g. > > (substr(pcmacaddress,10,2) || > substr(smarts.pcmacaddress,13,2) || > substr(smarts.pcmacaddress,16,2) || > substr(smarts.pcmacaddress,19,2) || > substr(smarts.pcmacaddress,22,2) || > substr(smarts.pcmacaddress,25,2))(+)=lisa.macaddress > makes smarts the deficient table, but > smarts.giltbis = '2100-01-01-00.00.00.000000' and eliminates the outer-ness of the join, and loses any appended rows. similarly with: > kompsys.system = syspers.system(+) and > syspers.giltbis = '2100-01-01-00.00.00.000000' and the join to syspers ceases to be outer because of the literal comparison. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______April 8th ____UK_______April 22nd ____Denmark May 21-23rd ____USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____UK_(Manchester)_May ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 20 March 2003 14:44 > > Hello, List: > I'm doing a outer join with SUBSTR, but can't get it to work. Is there a way to get correct or get around it? Thanks in advance > > SELECT rtrim(komp.inventar), > rtrim(komp.typ), ... > rtrim(smarts.giltbis) > FROM kompneti, komp, sysstand, person, c_lisa_data lisa, kompsys, syspers, c_smarts_data smarts > WHERE kompneti.mac_adr = lisa.macaddress and > kompneti.giltbis = '2100-01-01-00.00.00.000000' and > kompneti.ident = komp.ident and > komp.ident = kompsys.ident and > kompsys.giltbis = '2100-01-01-00.00.00.000000' and > kompsys.system = sysstand.system and > sysstand.giltbis = '2100-01-01-00.00.00.000000' and > kompsys.system = syspers.system(+) and > syspers.giltbis = '2100-01-01-00.00.00.000000' and > syspers.pers_nr = person.pers_nr(+) and > smarts.giltbis = '2100-01-01-00.00.00.000000' and > lisa.macaddress = substr(pcmacaddress,10,2) || > substr(smarts.pcmacaddress,13,2) || > substr(smarts.pcmacaddress,16,2) || > substr(smarts.pcmacaddress,19,2) || > substr(smarts.pcmacaddress,22,2) || > substr(smarts.pcmacaddress,25,2) (+) > / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).