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