LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: joygupta

Hi,

We have to update a table with a datafile every month. This datafile may contain new data or same data or changes to existing data in table. Datafile has subset of columns to table.

I can find new and existing data but not the rows with changes to existing data, which can be on key or non-key columns.

Is there a way to achieve this and what method to use, I am using x-ref node to join the inputs.

Thanks.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

I'm not sure exactly what you are asking...are you saying you would like to be able to take a set of data in, and compare it to some that is already loaded, and figure out what has changed? Do you have a unique value to join against? Are there a limited set of fields you want to compare to? Are you looking to separate into just two groups, those that are the same and those that are different?

If I understand your problem, and the constraints, could you just cat the two sets of data together, and then agg on any field or set that should be unique, and make sure there are two in each of those group that are the same? you could do that by comparing all the relevant fields, or by writing a python node that stores and compares the entire record to make sure the two are the same. If I am on the right track, let me know, and I can maybe provide a better example.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: johnpelz

Hey Joy,

As we discussed, attached is the "enhanced" Join node that addresses your needs. Obviously, it has not been tested by our engineering group or is it part of an official release, so use it with caution.

Assumptions:
- Data is sorted on the key field(s)
- Data types are consistent for column level comparison
- Metadata is consistent (same column names / types)

Let me know if you have any other questions or concerns,
-John
Attachments:
Enhanced Join.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mdsdavidm

I've tried the enhanced join node which was recently added to this post.
I'm using it to compare the data from two versions of an iSeries table and it's correctly finding the differences.
The problem I have is to do with the output produced in output 3 for the partial matches.
The field being compared is numeric and on one version of the file contains 1000000 and the other 1000001 but in the output produced both columns contain 1e+006 and if I write the info to a csv file both columns contain 1000000.
As I'm new to LAE and in particular the use of Brainscript do you know what I need to do to format the information in these columns correctly.
Thanks for any help that you can give me,
David Merriles
MDS

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

I have just tried to reproduce the problem, and I think it might have to do with your version of software. Which version of the software are you running? Can you post an example graph that demonstrates your issue? I tried populating the example above with other data to reproduce what you described, and was unable to, using the latest version of our software. I also believe there was an improvement made in this regard a few versions back.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Hi Team,

A followup question to this. Is there a way to update a field in a database table based on the values of another field?

Eg: update table1 set field1='X' where field2 in ('1','2','3','4');

How do i do this in lavastorm nodes?

Thanks,
Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

That is simply a standard SQL statement. In a DbExecute node:

sqlNonSelect("update table1 set field1='X' where field2 in ('1','2','3','4');")