DSXchange

Posted: **Wed Dec 17, 2003 9:26 pm**

Hi,

Is there any way to compare 2 hash files without exporting them to a sequential file?
In addition is there any way to copy and paste rows out of a hash file?

Thanks
Brad

Posted: **Wed Dec 17, 2003 9:39 pm**

Please outline your functional requirements. You can always have a job read hash file A and reference hash file B and output a sequential file of rows that are different, but you'll have to construct the constraint to do the comparison. You know, inA.col1 <> inB.col1 OR inA.col2 <> inB.col2 blah blah blah.

Posted: **Thu Dec 18, 2003 12:12 am**

Is there any way to do this with an Oracle table?

A hashed file is a table in the UniVerse database (aka the DataStage Engine database).

If the answer is no in one, it's no in the other.

If you want to access rows in a hashed file, you use the correct query languages, just like you would with other databases, except that there are two languages - Retrieve and SQL.

What, exactly, do you mean by compare? If you want to know whether every row in hashed file A has an exact counterpart in hashed file B, then you could use a DataStage job, or even a pair of UniVerse SQL commands to check each possible DIFFERENCE.

You need to specify your requirement more exactly.

Posted: **Thu Dec 18, 2003 2:36 am**

To find keys in one hash file and not in the other

Code: Select all

SELECT Hash1
NSELECT Hash2
SAVE.LIST NotIn2
GET.LIST NotIn2
LIST Hash1

Code: Select all

SELECT Hash2
NSELECT Hash1
SAVE.LIST NotIn1
GET.LIST NotIn1
LIST Hash2

NSELECT will build a list of keys not in the hash file. SAVE.LIST will save this list of keys so you can use it over and over with a GET.LIST command.

Code: Select all

open "HashFile1" to Hash1 else stop
open "HashFile2" to Hash2 else stop
DiffKeys = ''
SELECT Hash1
loop while readnext Key1
  RecDiff = @true
  read Rec1 from Hash1, Key1 then
    * Thx Ray Key2 was changed to Key1
    read Rec2 from Hash2, Key1 then 
      if Rec1 = Rec2 then
        RecDiff = @false
      end
     end
  end
  if RecDiff then 
    DiffKeys<-1> = Key1
  end
repeat
**
* write DiffKeys to some file
**

Kim.

Posted: **Thu Dec 18, 2003 4:54 am**

... or even a query (for example, for one side of the difference):

Code: Select all

SELECT hashedfile1 WITH EVAL "@RECORD" <> EVAL "TRANS('hashedfile2', @ID, -1, 'X')"