DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: sqlplus matching

  1. #11
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    We'll be using SOUNDEX next!


  2. #12
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Originally posted by kris123
    thanks pando....but is "regular expression" a new feature in 10g?
    yes it is, althought you have some very limited functionality in owa_pattern in 9i

  3. #13
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter

  4. #14
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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

  5. #15
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

    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

  6. #16
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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
    Maybe you have more info to add?
    In this case take it from Jeff's word:
    Can you say "full table scan baby"? Also, this method has tons of potential for error... As pando says, make your data clean.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width