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

Hello, List:
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">
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  /
 </BLOCKQUOTE> <hr size=1>Do you Yahoo!? 
<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