Compare two Oracle table

[slk200]

Hi All,

what is the easiest way to compare two oracle tables with 25 attributes and up to 100.000 rows and write out the differences ?

Thx
Matthias

[Sainath.Srinivasan]

It depends on what is in the tables.

You can use Oracle join to check it and return values.

Otherwise you can download them in a seq file and do an OS level comparison.

[I_Server_Whale]

Hi,
You can also use the CRC-32 routine and compare as many attributes as you want. Hope that helps.

Naveen.

[kduke]

The MINUS command is also powerful.

[kollurianu]

Hi All,

Where is the CRC-32 routine available under which category,

for what it is exactly used?

any answers higly appreciated.

Thank you all

[I_Server_Whale]

Hi Annapurna,
The CRC32 routine is used to compare records from two tables or two flatfiles. The CRC32 algorithm concatenates each column of the each table and generates a unique 32-bit number for each record on that table. So, this way, you can generate the CRC32 number for the record in the other table also and THEN compare both of them because all you have to do is compare the number generated by CRC32.

Hope it helps. If not, let me know where you are having problems.

Naveen.

[kollurianu]

Hi Naveen ,

thanks for ur answer , can i compare about 10 fields to 2 files on in
sequential one in hash file and can i use this in a transformer constraint
where is this routine available under which category.

any answers would be appreciated,

thank you all,

[I_Server_Whale]

Annapurna,
I have not understood the first part of your question. Can you frame it again? But as far as the routine and the category. The CRC32 routine is not under any category.

As far as I remember, the syntax for CRC32 is :

CRC32(column1:column2:column3:........:columnn)

This generates a unique number. And you can play around with that number according to your requirement.

Remember, you are concatenating the columns.

Naveen.

[kollurianu]

Hi Naveen,


Iam sorry for framing my question in an unclear way ,

1. i couldnot see the routine CRC32 in the manager.

2.i need to compare fields in a sequential stage and fields in a
hash file stage , and can i use this routine in the transformer
constraint.

Thank you all very much.

[I_Server_Whale]

Hi,
You cannot find the routine in the manager. You can compare the fields in both the stages by writing the constraint in the transformer.

You can also use stage variables for the each CRC32 generated, one for sequential and one for hash.

Use can say for example;

A= CRC32(field1:field2:field3............)

Let me know if its still not clear. And let me know where you are having trouble understanding.

Naveen.

[pnchowdary]

2.i need to compare fields in a sequential stage and fields in a
hash file stage

Hi Anu,

To compare fields in a sequential stage and a hash file stage, Wouldnt it be easy to use the hash file as the lookup and the records from your input sequential file as the stream input. In fact the primary use of the hash files is for lookup.

Thanks,
Naveen

[kollurianu]

Hi Naveen,

thanks for ur reply , where is the CRC32 routine found in manager ,
i could not see the routine in the manager, could you please let
me know where it is found.

how does the string COMPARE function work?

waiting for all the inputs.

Thank you,

[I_Server_Whale]

Please do read the message properly. I said you CANNOT find it in the manager. It is not listed, I don't know the reason.

But you can use it as I explained. It will surely work.
Well! It worked for me

Naveen.

[kollurianu]

hey Naveen,


i found it , it is listed under functions , thank you very much for your
answers.

Thank you all once again.

[kollurianu]

how does CRC32 and Checksum function differ?

any answers greatly appreciated