Comparing Data Structures

[jj_dahlheimer]

The short question is I am looking for the most efficient way to compare two data structure arrays, I currently having it working with loops but it takes a couple of minutes to run and it seems like there is a better way.

The long explanation why. The process I have is to compare two files that have a totally different structure, our old order format(file described and out of date) and our new one(shiny new sql table). Before switching I need to run these comparisons for a couple of weeks and make sure that the conversions are taking place properly and there aren't any discrepancies between the two files. There aren't any rules for these files just a bunch of records with store, item, qty in them so I figured the easiest and most bullet proof way was to read the two files into data structures defined just like that and then compare the two and blank out matching records. When I get to the end all that is left is ones that have not matched. I just read through the structures one more time and print them out. It seems to work just want to try and make it a little faster.

[B.Hauser]

Re: Comparing Data Structures

Does that mean, you have 2 different tables, with columns with different names and different order that must be compared for all rows?
If the columns have compatible data types you may try the following SQL statement.
The statement will return *Zero rows if all column and row values are identical in both tables.

   (   Select Col1, Col2, Col3, ... ColX
          From Table1
          Where ....
    Except
        Select ColA, ColC, ColF, ... ColN  -- same number of columns / data types must be compatible
          From Table2
          Where ....)
Union
   (   Select ColA, ColC, ColF, ... ColN
          From Table2
          Where ....
    Except
        Select Col1, Col2, Col3, ... ColX
          From Table1
          Where ....)

If the data types are not compatible you may try to cast the columns to compatible data types.

Birgitta

[jj_dahlheimer]

Re: Comparing Data Structures

Our old order file is program described and its format is store, qty1, item1, qty2, item2 etc until 6. Pain to deal with, I thought about trying to write it out to a temporary file and then use sql but figured the data structures would be simpler.

[DAG0000]

Re: Comparing Data Structures

Load both into matching data structures, then just compare the 2.

[jj_dahlheimer]

Re: Comparing Data Structures

That is what I am doing with loops but it seems rather inefficient, I was just making sure there was not a better way.

[DAG0000]

Re: Comparing Data Structures

I don't know what your mean by loops. Is that for retrieving like records between the files or part of loading the data structures for comparison? You may want to post some code to show how your attempting this.

[jj_dahlheimer]

Re: Comparing Data Structures

First I read through the new order file and populate a data structure and then I do the same with the old file. Both are qualified array data structures, this part does not take long. Then though I have to go through every entry in the first data structure and compare it to every record in the second data structure and blank out the matching ones, leaving the unmatched entries. This takes a couple of minutes, figured there was a better way but the only thing I could find was the lookup function in 7.1, and I don't have 7.1 and that didn't even seem like it was a good fit either. Below is the code.

         i = 1;
         DoW i < nfileCount;
           j = 1;
           Dow j < ofileCount;
             If nfile(i) = ofile(j);
               nfile(i) = *Zero;
               ofile(j) = *Zero;
             EndIf;
             j += 1;
           EndDo;
           i += 1;
         EndDo;

[DAG0000]

Re: Comparing Data Structures

I would move those qualified DS's via eval-core into 2 new qualified DS's with the same layout. Then you could then just compare the the first DS against the second and not got through each sub field.

File1 load into DS1
File2 load into DS2

Have 2 duplicate DS's, DSA and DSB that are exactly the same that have all the fields you want to compare.

Use Eval-Core to move DS1 into DSA
Use Eval-Core to move DS2 into DSB
Compare DSA to DSB

[GLS400]

Re: Comparing Data Structures

Hi jj_dahlheimer:

I don't know if this is any better.....

nfile/ofile = qtys?????
If so I added nitem/oitem arrays....

         i = 1;
         DoW i < nfileCount;
           j = %lookup(nitem(I) : oitem);
            if j <> 0 ;
               nfile(i) = *Zero;
               ofile(j) = *Zero;
            EndIf;
           i += 1;
         EndDo;

Not tested but you should get the idea.

Best of Luck
GLS

[jj_dahlheimer]

Re: Comparing Data Structures

@DAG - How would I get the differences between DSA and DSB? I think that would tell me they are not equal but I have to figure out the differences so that I can figure out why they are different.

@GLS - That is how I figured it would work but %LOOKUP does not do Data Structures until 7.1. Should have specified that nfile and ofile are data structures so in my loop I am looking at each element of the data structure(store,item,qty) and if it matches to an element of the other one I am blanking them so that I can print out the differences in the end.

[Scott Klement]

Re: Comparing Data Structures

I think the problem is that you are spinning through the entire 'ofile' structure every occurrence of the nfile structure.

Is it possible to sort the structures, so they're in the same sequence? then you wouldn't have to read the whole thing each time, you could just spin until you find one that's greater than the current one to see if there might be a matching element... much like the way matching records logic works in the cycle.

Or if you do need to search your 'ofile' structure for each iteration, use a binary lookup...

[DAG0000]

Re: Comparing Data Structures

Once you know the records records match you can blank everything and only do a field by field comparison for mismatches.

[GLS400]

Re: Comparing Data Structures

Way back (about 100 years ago)
You could build the array element using a data structure.
pseudo code:

d                      ds
d store                          1     2s 0
d Item                           3    15a
d Qty                           16    20s 0
d element                        1    20a


c             Read oldfile                  99
c             add   1                        x
c             move element               ofile(x)

do the same for newfile

Now you have 2 arrays not a data structure
Not at all elegant but functional

Best of Luck
GLS

[tomliotta]

Re: Comparing Data Structures

Our old order file is program described and its format is store, qty1, item1, qty2, item2 etc until 6. Pain to deal with, I thought about trying to write it out to a temporary file and then use sql but figured the data structures would be simpler.

I'd still probably create an external file description that matches the program description... maybe unless you have a multi-format program-described file. If you have description, you might use it to help manipulate values in any program not just this one. Once it's defined and works (even though it contains no data), the description can be useful in multiple ways.

Is this an actual old S/36 file that resides in QS36F? Trying to get an overview of the whole problem.

[B.Hauser]

Re: Comparing Data Structures

If it's a program described file you may write an external UDTF and use this UDTF in composition with the SQL statement I've mentioned before.
RPG and Cobol Programs can handle internally described files quite easily. You only need to return the result read into the internally described data structures as columns.
An example for an external UDTF that reads an internally described file can be found here:
The Power of User Defined Table Functions

Birgitta