Compare two Oracle table

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jul 12, 2005 3:45 pm

CRC32 is a 32 bit fn and CheckSum is a 16 bit fn. So CRC32 has more probability of returning different checksum values when the source is different.

slk200 · Post by **slk200** » Wed Jul 13, 2005 4:06 am

Thx to all of you - but i still have some problems:

i created two stage variables 'A' and 'B'

A =
CRC32(FromTRS00_i.ACC_YEAR:FromTRS00_i.FINANCIAL_YEAR:FromTRS00_i.TREATY:FromTRS00_i.TREATY_SECTION:FromTRS00_i.PERIOD_START_DATE:FromTRS00_i.PERIOD_END_DATE:FromTRS00_i.RESP_DEPARTMENT:FromTRS00_i.RESP_DIVISION:FromTRS00_i.RESP_RESSORT:FromTRS00_i.LINE_OF_BUSINESS:FromTRS00_i.LOB_HIERARCHY_1:FromTRS00_i.LOB_HIERARCHY_2:FromTRS00_i.CLASS_OF_BUSINESS:FromTRS00_i.LIFE_OR_NON_LIFE:FromTRS00_i.PLANNING_SEGMENT:FromTRS00_i.TREATY_CATEGORY:FromTRS00_i.NATURE_OF_TREATY:FromTRS00_i.GROUP_CATEGORY:FromTRS00_i.GROUP_CAT_VALUE:FromTRS00_i.BUSINESS_TYPE:FromTRS00_i.FAS_CLASSIFICATION:FromTRS00_i.RI_CHARACTER:FromTRS00_i.SYMBOLISATION:FromTRS00_i.CEDENT:
FromTRS00_i.CEDENT_GROUP:FromTRS00_i.CEDENT_COUNTRY:FromTRS00_i.COMPANY_CODE:FromTRS00_i.PARTNER_INVOLVED:FromTRS00_i.AREA:FromTRS00_i.LEGAL_PARTNER:FromTRS00_i.PL_SEG:FromTRS00_i.MGF_FLAG)

and B =
CRC32(FromTRS00.ACC_YEAR:FromTRS00.FINANCIAL_YEAR:FromTRS00.TREATY:FromTRS00.TREATY_SECTION:FromTRS00.PERIOD_START_DATE:FromTRS00.PERIOD_END_DATE:FromTRS00.RESP_DEPARTMENT:FromTRS00.RESP_DIVISION:FromTRS00.RESP_RESSORT:FromTRS00.LINE_OF_BUSINESS:FromTRS00.LOB_HIERARCHY_1:FromTRS00.LOB_HIERARCHY_2:FromTRS00.CLASS_OF_BUSINESS:FromTRS00.LIFE_OR_NON_LIFE:FromTRS00.PLANNING_SEGMENT:FromTRS00.TREATY_CATEGORY:FromTRS00.NATURE_OF_TREATY:FromTRS00.GROUP_CATEGORY:FromTRS00.GROUP_CAT_VALUE:FromTRS00.BUSINESS_TYPE:FromTRS00.FAS_CLASSIFICATION:FromTRS00.RI_CHARACTER:FromTRS00.SYMBOLISATION:FromTRS00.CEDENT:
FromTRS00.CEDENT_GROUP:FromTRS00.CEDENT_COUNTRY:FromTRS00.COMPANY_CODE:FromTRS00.PARTNER_INVOLVED:FromTRS00.AREA:FromTRS00.LEGAL_PARTNER:FromTRS00.PL_SEG:FromTRS00.MGF_FLAG)

...you see - there are two differnt ora-tables: FromTRS00 and FromTRS00_i

...when i write 'A' and 'B' to a flat file i only got the delimiter in the flat file ??

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 13, 2005 4:08 am

Check for NULL values.

I_Server_Whale · Post by **I_Server_Whale** » Wed Jul 13, 2005 9:14 am

Thats correct! As Sainath said, you should be careful not to pass NULL values into the CRC32 routine. Hope that helps.

Naveen.

logic · Post by **logic** » Wed Jul 13, 2005 1:21 pm

Hi Annapurna,
Also want to add a comment.. As Sai Said handle "NULL" values with care while calculating CRC32. It does not like NULLS.
If Date fields are coming in try the NullToEmpty (Courtesy ADN) or for integers coming in as Nulls NullToZero should work. Am I correct Sai?
Thanks,
Ash.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 13, 2005 1:52 pm

That is correct. You can use the NullToEmpty or NullToZero. Or you can use NVL in Oracle itself.

slk200 · Post by **slk200** » Tue Jul 19, 2005 6:24 am

Thx to all of you - <NVL> is the key to the solution - thx again

bibhudc · Post by **bibhudc** » Tue Jul 19, 2005 10:38 am

kduke wrote:The MINUS command is also powerful.

Use the Minus command with caution. If any of the columns has NULLs, then the minus command willnot work.

ranga1970 · Post by **ranga1970** » Tue Jul 19, 2005 10:46 pm

Hi;
I was told CRC32 had some limitation, could some one please trhough some light..

ray.wurlod · Post by **ray.wurlod** » Wed Jul 20, 2005 12:45 am

Why not ask whomever told you, and enlighten us with their wisdom?