Re: Try Again: Outer Join with SUBSTR
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.00' 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.00' 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.00' and > kompneti.ident = komp.ident and > komp.ident = kompsys.ident and > kompsys.giltbis = '2100-01-01-00.00.00.00' and > kompsys.system = sysstand.system and > sysstand.giltbis = '2100-01-01-00.00.00.00' and > kompsys.system = syspers.system(+) and > syspers.giltbis = '2100-01-01-00.00.00.00' and > syspers.pers_nr = person.pers_nr(+) and > smarts.giltbis = '2100-01-01-00.00.00.00' 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).
RE: Try Again: Outer Join with SUBSTR
Title: RE: Try Again: Outer Join with SUBSTR SQL> select * from t ; V ABCD WXYZ SQL> select * from t2 ; V -- ABQCDR WXQYRZ SQL> select a.v, b.v 2 from t a, t2 b 3 where a.v = substr (b.v(+), 1, 2) || substr (b.v(+), 4, 2) ; V V -- ABCD ABQCDR WXYZ -Original Message- From: WLSH [mailto:[EMAIL PROTECTED]] 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(komp.herst), rtrim(sysstand.so_nr), rtrim(person.vname), rtrim(person.nname), rtrim(lisa.assetnumber), rtrim(lisa.unittype), rtrim(lisa.macaddress), rtrim(lisa.ipaddress), rtrim(lisa.operatingsystem), rtrim(lisa.servername), rtrim(lisa.lastrundate), rtrim(lisa.serialnumber), rtrim(lisa.person), rtrim(lisa.location), rtrim(smarts.pcmacaddress), rtrim(smarts.pcip), rtrim(smarts.portname), rtrim(smarts.portdesc), rtrim(smarts.switchname), rtrim(smarts.switchipaddres! s), rtrim(smarts.switchlocation), rtrim(smarts.vendor), rtrim(smarts.site), rtrim(smarts.custname), rtrim(smarts.scandate), 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.00' and kompneti.ident = komp.ident and komp.ident = kompsys.ident and kompsys.giltbis = '2100-01-01-00.00.00.00' and kompsys.system = sysstand.system and sysstand.giltbis = '2100-01-01-00.00.00.00' and kompsys.system = syspers.system(+) and syspers.giltbis = '2100-01-01-00.00.00.00' and syspers.pers_nr = person.pers_nr(+) and smarts.giltbis = '2100-01-01-00.00.00.00' 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) (+) / Or Use: 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 / Or Use: (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! /
