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
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.
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.
SQL> create table emp(empname varchar2(35), emp_id number(4) not null);