COMPARE can be combined with inline functions to do simpler, hopefully faster, compares..

Syntax

COMPARE (SELECT selectlist, inline_function(*) FROM source)
     TO (SELECT selectlist, inline_function(*) FROM target)
   WITH KEY (col1,...) ;
   [SAVE [TO target_clog] [AS 'name']]

or

COMPARE (SELECT selectlist, inline_function(*) FROM source)
     TO (SELECT selectlist, inline_function(*) FROM target)
   WITH KEY (chk) ;
   [SAVE [TO target_clog] [AS 'name']]

Arguments

Examples

Example tables are drawn from the imdb database, for instance:

  • cast_info (id, person_id, movie_id, person_role_id, note, nr_order, role_id)

  • movie_keyword (id, movie_id, keyword_id)

  • company_name(id, name, country_code, imdb_id, name_pcode_nf, name_pcode_sf)

Compare excluding the primary key when it is auto-generated.

COMPARE (SELECT TRIM(keyword) AS keyword, TRIM(phonetic_code) AS phonetic_code FROM lms.cirrodb.dbo.keyword)
     TO (SELECT TRIM(keyword) AS keyword, TRIM(phonetic_code) AS phonetic_code FROM lim.imdb.public.keyword)
   WITH KEY (keyword, phonetic_code) ;

Compare using a checksum of the row rather than comparing every cell.

COMPARE (SELECT id, ROWCHECKSUM(*) as chk FROM lms.cirrodb.dbo.company_name)
     TO (SELECT id, ROWCHECKSUM(*) as chk FROM lim.imdb.public.company_name)
   WITH KEY (id) ;

Compare using a checksum of the table rather than comparing every row.

COMPARE (SELECT country_code, CHECKSUM(*) as chk FROM lms.cirrodb.dbo.company_name GROUP BY 1)
     TO (SELECT country_code, CHECKSUM(*) as chk FROM lim.imdb.public.company_name GROUP BY 1)
   WITH KEY (country_code) ;

Compare checksums of the two tables rather than comparing every row.

COMPARE (SELECT CHECKSUM(*) as chk FROM lms.cirrodb.dbo.company_name)
     TO (SELECT CHECKSUM(*) as chk FROM lim.imdb.public.company_name)
   WITH KEY (chk) ;
COMPARE (SELECT id, ROWCHECKSUM(*) FROM ORACLE.orcl12.DEMO.T1)
     TO (SELECT id, ROWCHECKSUM(*) FROM ORACLE.orcl12.DEMO.T2)
   WITH KEY (id) ;

See Also