Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Posts
    6

    Question Unanswered: Matching Records Using LIKE or MATCH Condition

    Here is the sample data

    Table A

    Bank ID|Acct_number
    ID123|2245,3456,002245,1234
    ID123|77778900000000
    ID123|112244
    ID123|4455,6666,0004455

    Table B

    Bank ID|Acct_number
    ID123|2245-- This Should not be in the missing number file
    ID123|777789 -- This Should not be in the missing number file
    ID123|112244-- This Should not be in the missing number file
    ID123|6666--This should be in the missing file
    ID123|888889--This should be in the missing file
    ID123|667788--This should be in the missing file


    I am trying to find missing account numbers in table B that are not in table A using the query below, but it's not giving me the missing results.

    If you look at the data in table A there are multiple account numbers, and also leading zeros

    unload to missing_acct_numbers1.txt
    select tempb.di_id, tempb.acct_number from tempb ,tempa where
    tempb.di_id = tempa.di_id and
    tempb.acct_number = tempa.acct_number
    and tempb.acct_number NOTLIKE '%'||tempa.acct_number||'%'};

    Any suggestions will be helpful

    Thanks a lot in advance.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    this is never will work...
    how do you can get a data from a SQL when they don`t exists...

    If you put tempa as inner join , you will have only what are in common between tables. And with the last clause you just invert all logical , bring nothing...

    Try this... maybe need some adjusts..
    Code:
    unload to missing_acct_numbers1.txt
    select tempb.di_id, tempb.acct_number 
    from tempb 
    where
     not exists ( 
      select * from tempa where
        tempb.di_id = tempa.di_id and
       and tempa.acct_number LIKE '%'||tempb.acct_number||'%'
    );
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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