Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Try Again: Outer Join with SUBSTR

Try Again: Outer Join with SUBSTR

From: WLSH <wlsh1961_at_yahoo.com>
Date: Thu, 20 Mar 2003 06:44:07 -0800 (PST)
Message-Id: <24726.322537@fatcity.com>


--0-1572942585-1048171447=:70009
Content-Type: text/plain; charset=us-ascii

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(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.switchipaddress),
    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.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) (+) 

 /
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 
/  

Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! --0-1572942585-1048171447=:70009
Content-Type: text/html; charset=us-ascii

<P>Hello, List:
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">
<P>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</P>
<P>SELECT rtrim(komp.inventar),<BR>&nbsp;&nbsp;&nbsp; rtrim(komp.typ),<BR>&nbsp;&nbsp;&nbsp; rtrim(komp.herst),<BR>&nbsp;&nbsp;&nbsp; rtrim(sysstand.so_nr),<BR>&nbsp;&nbsp;&nbsp; rtrim(person.vname),<BR>&nbsp;&nbsp;&nbsp; rtrim(person.nname),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.assetnumber),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.unittype),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.macaddress),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.ipaddress),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.operatingsystem),&nbsp; <BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.servername),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.lastrundate),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.serialnumber),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.person),<BR>&nbsp;&nbsp;&nbsp; rtrim(lisa.location),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.pcmacaddress),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.pcip),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.portname),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.portdesc),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.switchname),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.switchipaddress),<BR>&nbsp;&nbsp;&nbsp; rtrim(
smarts.switchlocation),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.vendor),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.site),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.custname),<BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.scandate), <BR>&nbsp;&nbsp;&nbsp; rtrim(smarts.giltbis)<BR>FROM kompneti, komp, sysstand, person, c_lisa_data lisa, kompsys, syspers,&nbsp;&nbsp; c_smarts_data smarts <BR>WHERE kompneti.mac_adr = lisa.macaddress and <BR>&nbsp;kompneti.giltbis = '2100-01-01-00.00.00.000000' and <BR>&nbsp;kompneti.ident = komp.ident and <BR>&nbsp;komp.ident = kompsys.ident and <BR>&nbsp;kompsys.giltbis = '2100-01-01-00.00.00.000000' and <BR>&nbsp;kompsys.system = sysstand.system and <BR>&nbsp;sysstand.giltbis = '2100-01-01-00.00.00.000000' and <BR>&nbsp;kompsys.system = syspers.system(+) and <BR>&nbsp;syspers.giltbis = '2100-01-01-00.00.00.000000' and <BR>&nbsp;syspers.pers_nr = person.pers_nr(+) and <BR>&nbsp;smarts.giltbis = '2100-01-01-00.00.00.000000' and<BR><STRONG>&nbsp;lisa.macaddress = substr(pcmacaddress,10,2) || <BR>&nbsp;substr( smarts.pcmacaddress,13,2) || <BR>&nbsp;substr(smarts.pcmacaddress,16,2) || <BR>&nbsp;substr(smarts.pcmacaddress,19,2) || <BR>&nbsp;substr(smarts.pcmacaddress,22,2) || <BR>&nbsp;substr(smarts.pcmacaddress,25,2) (+) </STRONG><BR>&nbsp;/<BR>Or Use:<BR><STRONG>substr(pcmacaddress,10,2) || <BR>&nbsp;substr(smarts.pcmacaddress,13,2) || <BR>&nbsp;substr(smarts.pcmacaddress,16,2) || <BR>&nbsp;substr(smarts.pcmacaddress,19,2) || <BR>&nbsp;substr(smarts.pcmacaddress,22,2) || <BR>&nbsp;substr(smarts.pcmacaddress,25,2)(+)=lisa.macaddress</STRONG>&nbsp;<BR>&nbsp;/<BR>Or Use:<BR><FONT color=#0000bf><STRONG>(</STRONG></FONT>substr(pcmacaddress,10,2) || <BR>&nbsp;substr(smarts.pcmacaddress,13,2) || <BR>&nbsp;substr(smarts.pcmacaddress,16,2) || <BR>&nbsp;substr(smarts.pcmacaddress,19,2) || <BR>&nbsp;substr(smarts.pcmacaddress,22,2) || <BR>&nbsp;substr(smarts.pcmacaddress,25,2)<STRONG><FONT color=#0000bf>)</FONT>(+)=lisa.macaddress&nbsp;<BR>/</STRONG></P>
<P><STRONG>&nbsp;</P></STRONG></BLOCKQUOTE><p><br><hr size=1>Do you Yahoo!?<br>
<a href="http://rd.yahoo.com/platinum/evt=8162/*http://platinum.yahoo.com/splash.html">Yahoo! Platinum</a> - Watch CBS' NCAA March Madness, <a href="http://rd.yahoo.com/platinum/evt=8162/*http://platinum.yahoo.com/splash.html">live on your desktop</a>!
Received on Thu Mar 20 2003 - 08:44:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US