hello I'm curious how you would deal with the following.
Table t1 has mixture of usernames, some with format of email address, some with format of first_name.last_name.
Table t2 lookup table has username in email format.
Requirement to return all 3 rows
create table t1 (taskid number, username varchar2(50))
insert into t1 values (1,'john.smith@12]3.com');
insert into t1 values (2,'dave.jones@123.com');
insert into t1 values (3,'dave.smith');
commit;
create table t2 (username varchar2(50));
insert into t2 values ('john.smith@123.com');
insert into t2 values ('dave.smith@123.com');
commit;
Via ansi syntax, quite forward:
select *
from t1
left outer join t2
on t1.username = t2.username
or
t1.username = substr(t2.username,1,instr(t2.username,'@')-1)
order by 1
Via oracle syntax - As can't use outer joins with OR, do you have any (easier) alternatives than this?
select t1.taskid, t1.username, t2.username
from t1, t2
where t1.username = t2.username
union all
select t1.taskid, t1.username, t2.username
from t1, t2
where t1.username = substr(t2.username,1,instr(t2.username,'@')-1)
union all
select t1.taskid, t1.username, to_char(null)
from t1
where not exists
(select 1
from t2
where t1.username = t2.username
or t1.username = substr(t2.username,1,instr(t2.username,'@')-1))
order by 1
thanks
A lateral does the trick nicely
SQL> select t1.*, x.username
2 from t1,
3 lateral(
4 select t2.username
5 from t2
6 where t1.username = t2.username
7 or t1.username = substr(t2.username,1,instr(t2.username,'@')-1)
8 ) (+) x;
TASKID USERNAME USERNAME
---------- -------------------- --------------------
1 john.smith@12]3.com
2 dave.jones@123.com
3 dave.smith dave.smith@123.com