I have two interfaces that parse the data and put the converted data into the table called rbc_swift_parser.
One interface (old) is written in C and the other in Java. Java one is still in a test environment. I have one primary key called host_reference. A single deal will have two rows in the table one is parsed by the Java interface and the other is parsed by the C interface. Can I compare each column (the total columns are 103) for a single deal? In short, my query will show which columns do not match each other. I tried using partition but it is not working.
Query:
select * from rbc_swift_parser where deal_number = '11NOV2400000018'
The above query will show two rows. I have read-only access to DB. Query I am using:
SELECT
a.host_reference,
a.deal_number,
CASE WHEN a.column1 <> b.column1 THEN 'Mismatch' ELSE 'Match' END AS column1_comparison,
CASE WHEN a.column2 <> b.column2 THEN 'Mismatch' ELSE 'Match' END AS column2_comparison,
-- Repeat for all 103 columns
CASE WHEN a.column103 <> b.column103 THEN 'Mismatch' ELSE 'Match' END AS column103_comparison
FROM
rbc_swift_parser a
JOIN
rbc_swift_parser b
ON
a.deal_number = b.deal_number
AND a.host_reference <> b.host_reference
WHERE
a.deal_number = '11NOV2400000018';
Not quite sure what you want to do - your query should give you missmatches. It's just to much code to write for all those columns.
-- S a m p l e D a t a :
Create Table rbc_swift_parser ( host_reference Number(6) Primary Key,
deal_number Number(6),
column1 Varchar2(32),
column2 Varchar2(32),
column3 Varchar2(32),
--
column103 Varchar2(32) );
Insert Into rbc_swift_parser
Select 1, 101, 'col_1 A', 'col_2 B', 'col_3 C', 'col_103 Z' From Dual Union All
Select 2, 101, 'col_1 A', 'col_2 X', 'col_3 C', 'col_103 Z' From Dual Union All
--
Select 3, 222, 'col_1 Y', 'col_2 B', 'col_3 C', 'col_103 Z' From Dual Union All
Select 4, 222, 'col_1 A', 'col_2 B', 'col_3 C', 'col_103 Z' From Dual Union All
--
Select 5, 303, 'col_1 A', 'col_2 B', 'col_3 C', 'col_103 Z' From Dual Union All
Select 6, 303, 'col_1 A', 'col_2 B', 'col_3 C', 'col_103 Z' From Dual;
-- Y o u r S Q L c o d e :
SELECT
a.host_reference,
a.deal_number,
CASE WHEN a.column1 <> b.column1 THEN 'Mismatch' ELSE 'Match' END AS column1_comparison,
CASE WHEN a.column2 <> b.column2 THEN 'Mismatch' ELSE 'Match' END AS column2_comparison,
-- Repeat for all 103 columns
CASE WHEN a.column103 <> b.column103 THEN 'Mismatch' ELSE 'Match' END AS column103_comparison
FROM
rbc_swift_parser a
JOIN
rbc_swift_parser b
ON
a.deal_number = b.deal_number
AND a.host_reference <> b.host_reference
WHERE
a.deal_number = '101';
HOST_REFERENCE DEAL_NUMBER COLUMN1_ COLUMN2_ COLUMN103
-------------- ----------- -------- -------- ---------
2 101 Match Mismatch Match
1 101 Match Mismatch Match
Maybe you could consider creating a PLSQL procedure or function to do the checking of missmatched data ...
... something like below ...
SET SERVEROUTPUT ON
Declare
Cursor t IS
Select Distinct DEAL_NUMBER From RBC_SWIFT_PARSER;
Cursor c IS
Select COLUMN_NAME
From all_tab_columns
Where OWNER = 'TABLE_OWNER_NAME' And TABLE_NAME = 'RBC_SWIFT_PARSER' And COLUMN_ID > 2;
v_col VarChar2(32);
v_deal_num Number(6) := 101;
v_sql VarChar2(4000);
v_result VarChar2(4000) := 'Missmatched: ';
Begin
Open t;
Loop
Fetch t Into v_deal_num;
Exit When t%NOTFOUND;
Open c;
LOOP
Fetch c Into v_col;
Exit When c%NOTFOUND;
v_sql := 'Select Distinct CASE WHEN a.' || v_col || ' != ' || 'b.' || v_col || ' ' ||
'THEN ' || '''' || v_col || '''' || ' ' ||
'ELSE ''OK'' END as col_name ' ||
'From RBC_SWIFT_PARSER a ' ||
'Inner Join RBC_SWIFT_PARSER b ON(a.DEAL_NUMBER = b.DEAL_NUMBER And b.HOST_REFERENCE != a.HOST_REFERENCE) ' ||
'Where a.DEAL_NUMBER = ' || v_deal_num;
Execute Immediate v_sql Into v_col;
If v_col != 'OK' Then
v_result := v_result || Chr(10) || Chr(9) || v_col || ' For Deal ' || v_deal_num;
End If;
END LOOP;
Close c;
End Loop;
Close t;
dbms_output.put_line(v_result);
End;
/
-- R e s u l t :
Missmatched:
COLUMN2 For Deal 101
COLUMN1 For Deal 222