Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: compare a table with a file.

    Hi everybody (yet another newbie )

    I have a table with 12,000 rows. I also have a file with 15,000 rows.

    One of the columns of the table has the field oid and that 7 digit number exists in the file with the 15,000 rows. I need to filter the file (15k rows) to the size of the table. Whenever the oid exists in the table I keep the row or whenever the oid filed does not exist in the table I drop the row off the file. I can extract the oid off the table but then I have to bash it the same way with file with the 15k rows. Is it better to do it in SQL or should I shellscript it between the two files? (in that case I should really post in the shellscript forums)

    thanks for helping out.

    Nick

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    First u load the flatfile in to a temporary table and use a delete statement to remove the non matching records from teh temp table

    Then again export the data from the temp table to flat file.

    If Original table is T1 & tmp table is T2

    delete from t2 where oid not in (select oid from t1)

  3. #3
    Join Date
    Dec 2003
    Posts
    3
    0002020002.000000008P0000000020031217142337CD N0100000226227884000020560116431834 00010001

    0002020002.000000008P0000000020031217142337CD N0100000226227884000020550118641512 00010001

    Thank you sir
    Ok I was thinking about it and this is what I thought as well.

    Now here is a copy of the content of the flat file:

    0002020002.000000008P0000000020031217142337CD N0100000226227884000020560116431834 00010001

    (this is one line it may wrap in the post). I am kind of green on SQL loader and I am niot sure how to apprach this. 560116431834 is the oid filed that I am interested. can you substr the SQL loader on character position like in sql?

    thanks a million for your help.

    Nick

  4. #4
    Join Date
    Dec 2003
    Posts
    3
    you could spool the required feild instead to the file....

    with the required set options ....

    thanks...

Posting Permissions

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