| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Try Again: Outer Join with SUBSTR
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C2EF03.78F90040
Content-Type: text/plain
SQL> select * from t ;
V
V V
---- ------
ABCD ABQCDR
WXYZ
-----Original Message-----
From: WLSH [mailto:wlsh1961_at_yahoo.com]
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)
kompsys.system = syspers.system(+) and syspers.giltbis = '2100-01-01-00.00.00.000000' and syspers.pers_nr = person.pers_nr(+) andsmarts.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) (+)
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
(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!/
------_=_NextPart_001_01C2EF03.78F90040
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =charset=3DUS-ASCII">
<TITLE>RE: Try Again: Outer Join with SUBSTR </TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>SQL> select * from t ;</FONT> <BR><FONT SIZE=3D2>V</FONT> <BR><FONT SIZE=3D2>----</FONT> <BR><FONT SIZE=3D2>ABCD</FONT>
<P><FONT SIZE=3D2>SQL> select * from t2 ;</FONT> <BR><FONT SIZE=3D2>V</FONT> <BR><FONT SIZE=3D2>------</FONT> <BR><FONT SIZE=3D2>ABQCDR</FONT> <BR><FONT SIZE=3D2>WXQYRZ</FONT> </P> <P><FONT SIZE=3D2>SQL> select a.v, b.v</FONT><BR><FONT SIZE=3D2> 2 from t a, t2 b</FONT> <BR><FONT SIZE=3D2> 3 where a.v =3D substr (b.v(+), 1, 2) = || substr (b.v(+), 4, 2) ;</FONT>
<P><FONT SIZE=3D2>V V</FONT> <BR><FONT SIZE=3D2>---- ------</FONT> <BR><FONT SIZE=3D2>ABCD ABQCDR</FONT> <BR><FONT SIZE=3D2>WXYZ</FONT> </P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: WLSH [<A =
HREF=3D"mailto:wlsh1961_at_yahoo.com">mailto:wlsh1961_at_yahoo.com</A>]</FONT>=
</P>
<P><FONT SIZE=3D2>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</FONT></P>
<P><FONT SIZE=3D2>SELECT rtrim(komp.inventar),</FONT> <BR><FONT SIZE=3D2> rtrim(komp.typ),</FONT> <BR><FONT SIZE=3D2> rtrim(komp.herst),</FONT> <BR><FONT SIZE=3D2> rtrim(sysstand.so_nr),</FONT> <BR><FONT SIZE=3D2> rtrim(person.vname),</FONT> <BR><FONT SIZE=3D2> rtrim(person.nname),</FONT> <BR><FONT SIZE=3D2> rtrim(lisa.assetnumber),</FONT> <BR><FONT SIZE=3D2> rtrim(lisa.unittype),</FONT> <BR><FONT SIZE=3D2> rtrim(lisa.macaddress),</FONT> <BR><FONT SIZE=3D2> rtrim(lisa.ipaddress),</FONT> <BR><FONT SIZE=3D2> =
<BR><FONT SIZE=3D2> rtrim(lisa.servername),</FONT> <BR><FONT SIZE=3D2> rtrim(lisa.lastrundate),</FONT> <BR><FONT SIZE=3D2> rtrim(lisa.serialnumber),</FONT> <BR><FONT SIZE=3D2> rtrim(lisa.person),</FONT> <BR><FONT SIZE=3D2> rtrim(lisa.location),</FONT> <BR><FONT SIZE=3D2> =
<BR><FONT SIZE=3D2> rtrim(smarts.pcip),</FONT> <BR><FONT SIZE=3D2> rtrim(smarts.portname),</FONT> <BR><FONT SIZE=3D2> rtrim(smarts.portdesc),</FONT> <BR><FONT SIZE=3D2> rtrim(smarts.switchname),</FONT> <BR><FONT SIZE=3D2> rtrim(smarts.switchipaddres! =s),</FONT>
<BR><FONT SIZE=3D2> rtrim(smarts.vendor),</FONT> <BR><FONT SIZE=3D2> rtrim(smarts.site),</FONT> <BR><FONT SIZE=3D2> rtrim(smarts.custname),</FONT> <BR><FONT SIZE=3D2> rtrim(smarts.scandate), </FONT> <BR><FONT SIZE=3D2> rtrim(smarts.giltbis)</FONT> <BR><FONT SIZE=3D2>FROM kompneti, komp, sysstand, person, c_lisa_data =lisa, kompsys, syspers, c_smarts_data smarts </FONT>
<BR><FONT SIZE=3D2>WHERE kompneti.mac_adr =3D lisa.macaddress and = </FONT> <BR><FONT SIZE=3D2> kompneti.giltbis =3D ='2100-01-01-00.00.00.000000' and </FONT>
<BR><FONT SIZE=3D2> kompneti.ident =3D komp.ident and </FONT> <BR><FONT SIZE=3D2> komp.ident =3D kompsys.ident and </FONT> <BR><FONT SIZE=3D2> kompsys.giltbis =3D ='2100-01-01-00.00.00.000000' and </FONT> <BR><FONT SIZE=3D2> kompsys.system =3D sysstand.system and </FONT> <BR><FONT SIZE=3D2> sysstand.giltbis =3D = '2100-01-01-00.00.00.000000' and </FONT>
<BR><FONT SIZE=3D2> kompsys.system =3D syspers.system(+) and = </FONT> <BR><FONT SIZE=3D2> syspers.giltbis =3D ='2100-01-01-00.00.00.000000' and </FONT>
<BR><FONT SIZE=3D2> syspers.pers_nr =3D person.pers_nr(+) and = </FONT> <BR><FONT SIZE=3D2> smarts.giltbis =3D ='2100-01-01-00.00.00.000000' and</FONT>
<BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,13,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,16,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,19,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,22,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,25,2) (+) </FONT> <BR><FONT SIZE=3D2> /</FONT> <BR><FONT SIZE=3D2>Or Use:</FONT> <BR><FONT SIZE=3D2>substr(pcmacaddress,10,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,13,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,16,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,19,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,22,2) || </FONT> <BR><FONT =
</FONT> <BR><FONT SIZE=3D2> /</FONT> <BR><FONT SIZE=3D2>Or Use:</FONT> <BR><FONT SIZE=3D2>(substr(pcmacaddress,10,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,13,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,16,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,19,2) || </FONT> <BR><FONT SIZE=3D2> substr(smarts.pcmacaddress,22,2) || </FONT>Received on Thu Mar 20 2003 - 11:09:31 CST
 |
 |