Results 1 to 8 of 8

Thread: match data

  1. #1
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23

    Unanswered: match data

    Hello, I need a help

    I have a table t1
    ids, barcode , title
    1 , 11112 , 'Me & others'
    2 , 11122 , 'Unknown'

    and I have a temp table from customers with barcodes and title
    barcode has to match our data.
    1111 , 'Me & others'
    1112 , 'Unknown'

    Customers barcodes has last digit cut of,
    My question is how to match this data if a last digit is missing?

    I need to create a table and insert
    our Id, our barcode , our title,
    their barcodefrom , title



    thank you

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    I guess , It should be something like this.

    PHP Code:
    INSERT INTO our_table
       
    (SELECT ida.barcoda.titleb.barcodeb.title
       FROM t1 a
    t2 b
       WHERE ti
    .barcode SUBSTR (t2.barcode1LENGTH (t1.barcode))); 
    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Sorry, It is Other way trun around.


    PHP Code:
    INSERT INTO our_table
       
    (SELECT ida.barcoda.titleb.barcodeb.title
       FROM t1 a
    t2 b
       WHERE t2
    .barcode SUBSTR (t1.barcode1LENGTH (t2.barcode))); 
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23

    compare identical parts in two strings

    Originally posted by Hings
    Sorry, It is Other way trun around.


    PHP Code:
    INSERT INTO our_table
       
    (SELECT ida.barcoda.titleb.barcodeb.title
       FROM t1 a
    t2 b
       WHERE t2
    .barcode SUBSTR (t1.barcode1LENGTH (t2.barcode))); 

    thank you but unforunately its not working
    I also find in some cases there a data with first or last digit missing,
    I need to compare identical parts in two strings

    lets say 01020333 and the other 102033
    second one is mssing first 0 and last 3 how to match/compare it?


    Any sugestion?
    Thank you

  5. #5
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    It works fine with me. Please look at below.

    PHP Code:
    SQLselect from t1;

            
    ID    BARCODE TITLE                                                     
    ---------- ---------- --------------------                                      
             
    1     111222 test row 1                                                
             2     222333 test row 2                                                
             3     333444 test row 3                                                

    SQL
    SELECT FROM t2;

       
    BARCODE TITLE                                                                
    ---------- --------------------                                                 
         
    11122 Match row 1                                                          
         22233 Match row 2                                                          
         33344 Match row 3                                                          

    SQL
    SELECT idt1.barcodet1.titlet2.barcodet2.title
      2  FROM t1
    t2
      3  WHERE t2
    .barcode SUBSTR (t1.barcode1LENGTH (t2.barcode));

            
    ID    BARCODE TITLE                   BARCODE TITLE                     
    ---------- ---------- -------------------- ---------- --------------------      
             
    1     111222 test row 1                11122 Match row 1               
             2     222333 test row 2                22233 Match row 2               
             3     333444 test row 3                33344 Match row 3 
    Thanks
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  6. #6
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23
    Originally posted by Hings
    It works fine with me. Please look at below.

    PHP Code:
    SQLselect from t1;

            
    ID    BARCODE TITLE                                                     
    ---------- ---------- --------------------                                      
             
    1     111222 test row 1                                                
             2     222333 test row 2                                                
             3     333444 test row 3                                                

    SQL
    SELECT FROM t2;

       
    BARCODE TITLE                                                                
    ---------- --------------------                                                 
         
    11122 Match row 1                                                          
         22233 Match row 2                                                          
         33344 Match row 3                                                          

    SQL
    SELECT idt1.barcodet1.titlet2.barcodet2.title
      2  FROM t1
    t2
      3  WHERE t2
    .barcode SUBSTR (t1.barcode1LENGTH (t2.barcode));

            
    ID    BARCODE TITLE                   BARCODE TITLE                     
    ---------- ---------- -------------------- ---------- --------------------      
             
    1     111222 test row 1                11122 Match row 1               
             2     222333 test row 2                22233 Match row 2               
             3     333444 test row 3                33344 Match row 3 
    Thanks

    thank you, I start testing my site and it seems its going to access every row from one tlb ant match to another in a second table, let see how long it will take , so far 10 min, no results back?

    thank you

  7. #7
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    It is an Untuned statement, you should tune it first to run on the production.

    If your database is Oracle 9i then, you can configure the resource group for that user and set the MAX_EST_EXEC_TIME. Oracle will return an error if your statement's total estimated execution time is more than that before actually executing the statement.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  8. #8
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23
    Originally posted by Hings
    It is an Untuned statement, you should tune it first to run on the production.

    If your database is Oracle 9i then, you can configure the resource group for that user and set the MAX_EST_EXEC_TIME. Oracle will return an error if your statement's total estimated execution time is more than that before actually executing the statement.

    thanks a lot id did finished and I got few results back out of 94.
    But another thing...
    it only matched those where all string is the same.
    How to match if first digit or last digit or both are missing?


    Thank you so much

Posting Permissions

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