cmp
Differences
This shows you the differences between two versions of the page.
|
cmp [2015/09/23 20:26] |
cmp [2015/09/23 20:26] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ====== CMP Statement ====== | ||
| + | |||
| + | CMP statement helps you compare data in tables that can be located in the same or different databases. | ||
| + | |||
| + | **Syntax**: | ||
| + | |||
| + | Compare the total number of rows: | ||
| + | |||
| + | <code language=sql> | ||
| + | CMP ROW_COUNT table1 [where_clause1] | (select_stmt1) [AT conn1], | ||
| + | table2 [where_clause2] | (select_stmt2) [AT conn2] | ||
| + | </code> | ||
| + | |||
| + | Compare the column summary (COUNT, SUM, MIN and MAX applied to columns): | ||
| + | |||
| + | <code language=sql> | ||
| + | CMP SUM table1 [where_clause1] [AT conn1], table2 [where_clause2] [AT conn2] | ||
| + | </code> | ||
| + | |||
| + | Notes: | ||
| + | * When data are equal, the CMP statement sets SQLCODE to 0. If data are not equal, SQLCODE is set to 1. In case of any SQL error, SQLCODE is set to -1 | ||
| + | * When the connection profile is not specified, the default connection profile is used for the specified table | ||
| + | |||
| + | **Example 1**: | ||
| + | |||
| + | Compare the number of rows: | ||
| + | |||
| + | <code language=sql> | ||
| + | CMP ROW_COUNT sales.users WHERE local_dt = CURRENT_DATE, users_daily AT mysqlconn; | ||
| + | </code> | ||
| + | |||
| + | **Example 2**: | ||
| + | |||
| + | Compare aggregate data in two tables: | ||
| + | |||
| + | <code language=sql> | ||
| + | CMP SUM sales.users WHERE local_dt = CURRENT_DATE, users_daily AT mysqlconn; | ||
| + | </code> | ||
| + | |||
| + | **Compatibility:** HPL/SQL Extension | ||
| + | |||
| + | **Version:** HPL/SQL 0.3.13 | ||
| + | |||
Page Tools
