Printable View
Hi Friends,
I have table EMP with column empname and another
LOANS_TABLE with column fullname.
EMP
======
EMPNAME
-----------
TOM WELLING
SCOTT WILSON
LOANS_TABLE
===========
FULLNAME
------------
WELLING, TOM
I want to list/match the employees with loans, eventhough lastname is
entered first in the loans table. In this case TOM WELLING is same
as WELLING, TOM
Can you help me please...
Thanks a lot
update your data first
Can you say "full table scan baby"? Also, this method has tons of potential for error... As pando says, make your data clean.Code:SQL> select * from xyz;
X
--------------------
elmer fudd
SQL> select * from abc;
Y
--------------------
fudd, elmer
SQL> edit
Wrote file afiedt.buf
1 select xyz.x, abc.y
2 from xyz, abc
3 where trim(xyz.x) =
4 trim(substr(y, instr(y,',')+1,
5 length(y)) || ' ' ||
6* substr(y,1, instr(y,',')-1))
SQL> /
X Y
-------------------- --------------------
elmer fudd fudd, elmer
Thanks to you my role-models;) ...im sorry i cant update the other apps tables coz its owned by other group and we have read only permission
hi "hun"...would it be slower or faster if i write it this way?
1 select A.x, B.y
2 from xyz A, abc B
3 where trim(A.x) =
4 trim(substr(B.y, instr(B.y,',')+1,
5 length(B.y)) || ' ' ||
6* substr(B.y,1, instr(B.y,',')-1))
SQL> /
What's different?
Thanks hun.....but there are still some records that can not be hit...
I want something that uses the "like" function... as in:
select x.empname, y.fullname from EMP x, LOANS_TABLE y
where y.fullname like '%TOM%' and y.fullname like '%WELLING%');
Can u help me split the empname to be similar to the above form?
Thanks again
Hi hun...fullname may contains more than two words like
LOANS_TABLE
===========
FULLNAME
-----------------
JOSE PROTACIO RIZAL MERCADO Y REALONDA
try regular expressions if you are using 10g
thanks pando....but is "regular expression" a new feature in 10g?
We'll be using SOUNDEX next!
:)
yes it is, althought you have some very limited functionality in owa_pattern in 9iQuote:
Originally posted by kris123
thanks pando....but is "regular expression" a new feature in 10g?
Look, you're trying to use technology to get around bad data. If the tables you don't own were designed by someone with half a brain they didn't use name as the primary key. You should use their primary key in your table so you don't have these problems.Quote:
Originally posted by kris123
Thanks to you my role-models;) ...im sorry i cant update the other apps tables coz its owned by other group and we have read only permission
If the person didn't have half a brain, then the format of your data should match their data. Then you wouldn't have to kludge this thing to get a decent join. Fact is, if there is no pattern to their data you can't write a function to join it.
I have to agree with the guys who replied your post,
but I know how difficult to propose changes to management here in our place.
I have a dirty but somehow nifty approach to solve your problem.
As what Jeff mentioned it is import to have keys in both tables,
I don't want to explain the script 'cause it's so basic. Your last step will be on how to tackle the records from both tables that wasn't included in the view if there is.
Hope this can help Ate.;)
SCRIPT:
SQL> create table emp(empname varchar2(35), emp_id number(4) not null);
Table created.
SQL> create table loans_table(fullname varchar2(30), loan_id number(4) not null)
SQL> /
Table created.
SQL> insert into emp values('TOM WELLING', 100);
1 row created.
SQL> ED
Wrote file afiedt.buf
1* insert into emp values('SCOTT WILSON', 101)
SQL> /
1 row created.
SQL> insert into LOANS_TABLE values('WELLING, TOM',2001);
1 row created.
SQL> ed
Wrote file afiedt.buf
1* insert into LOANS_TABLE values('WILSON, SCOTT',2002)
SQL> /
1 row created.
SQL> COMMIT;
SQL> SELECT * FROM EMP;
EMPNAME EMP_ID
----------------------------------- ----------
TOM WELLING 100
SCOTT WILSON 101
SQL> SELECT * FROM LOANS_TABLE
2 ;
FULLNAME LOAN_ID
------------------------------ ----------
WELLING, TOM 2001
WILSON, SCOTT 2002
CREATE VIEW LOAN_EMP_V AS SELECT LTRIM(SUBSTR(FULLNAME,INSTR(FULLNAME,',',1)+1,LENGTH(FULLNAME)
SUBSTR(FULLNAME,1, INSTR(FULLNAME,',',1)-1) FULLNAME, LOAN_ID
FROM LOANS_TABLE
/
SQL> SELECT * FROM LOAN_EMP_V;
FULLNAME LOAN_ID
------------------------------------------------------------- ----------
TOM WELLING 2001
SCOTT WILSON 2002
SQL> col fullname format a30
SQL> l
1 select emp_id, fullname, loan_id
2 from emp, loan_emp_v
3* where empname = fullname
SQL> /
EMP_ID FULLNAME LOAN_ID
---------- ------------------------------ ----------
101 SCOTT WILSON 2002
100 TOM WELLING 2001
Thanks to all of you my helpful kuya....honestly i have to analyze first what all you have written as it hardly sync into my brain :D
Just want to add some info about the simple function pattern example:
Table1:
=======
name1
----------------
TOM WELLING
TOM CRUISE
TOM PANGIT
PANGIT
Table2:
=======
name2
-------------------
WELLING CRUISE TOM POGI
IKAW AY PANGIT
Only table2 will serve as the source table to match with, while
table one is the transaction table.
Rows that matched in table1 are TOM WELLING, TOM CRUISE, and PANGIT coz all words are contained in rows in table2.
Thanks honieluv
Maybe you have more info to add?Quote:
Originally posted by kris123
Just want to add some info about the simple function pattern example:
Table1:
=======
name1
----------------
TOM WELLING
TOM CRUISE
TOM PANGIT
PANGIT
Table2:
=======
name2
-------------------
WELLING CRUISE TOM POGI
IKAW AY PANGIT
Only table2 will serve as the source table to match with, while
table one is the transaction table.
Rows that matched in table1 are TOM WELLING, TOM CRUISE, and PANGIT coz all words are contained in rows in table2.
Thanks honieluv
In this case take it from Jeff's word:
Quote:
Can you say "full table scan baby"? Also, this method has tons of potential for error... As pando says, make your data clean.