2

I wanted to know if we can get only the mismatched dataset from a table while doing volume testing please look at the below example:

If the below table is my target table which I perform my testing if the data is correctly loaded, I would write a SQL to select the columns from source and do a minus to the target table, if let's say the source has sal of XXX as 2000 then that's a mismatch from source to target, is it possible to get only id and sal column as output from the minus query.

To be clear if I have a table with 100 columns to do testing the minus query would return all the 100 columns even if there is a mismatch in 1 column so I want only 1 column to be returned which has the mismatch.

id  Name sal
--  ---- ----
1   XXX 1000
2   YYY 2000
3   ZZZ 4000
4   AAA 5000
harikamireddi
  • 27
  • 5
  • 1
    Which DBMS are you using? –  Jan 25 '16 at 23:22
  • what have you tried yourself? Hi and welcome to SO. Please add essential parts of your code/query. Read [how to ask](http://stackoverflow.com/help/how-to-ask) and [mcve](http://stackoverflow.com/help/mcve) for asking a better received question. – davejal Jan 25 '16 at 23:37
  • @a_horse_with_no_name its in oracle – harikamireddi Jan 26 '16 at 19:14
  • @davejal thanks, yes this is the first time. the query is too long to post so I just explained the scenario. I am validating a table from Test environment (tedw) to Prod environment(pedw).. so the table has 100 columns and need to find the mismatched columns, I am validating it with a minus query but it results in all the columns even if there is mismatch in one column and more over i have to manually check which column has the mismatch from the minus query result... so I want to avoid this and get only the mismatched columns as my result set. – harikamireddi Jan 26 '16 at 19:22
  • @harikamireddi did you find a useful way to do this – Kanav Sharma May 06 '20 at 09:17

3 Answers3

2

With the assumptions below

Both the source and target tables have a common primary Key

Then the a case statement can be used to narrow down on the exact column which is causing the mismatch

http://sqlfiddle.com/#!2/a78eca/16

select 
ST.id,
case 
when ST.name!=TT.name then 'Issue in name' 
When ST.sal!=TT.sal then 'Issue in Sal'
Else 'OK' END As Check_Column
From source_table ST, Target_table TT
where ST.id=TT.id
and (ST.name!=TT.name OR ST.sal!=TT.sal)

The explanation for above is that the where condition will filter out only the offending rows and the case statement will tell which row is causing the mismatch

SidD89
  • 21
  • 4
  • 1
    Hi sid thanks for the response, but as I said there are 80 columns in the table that need to be compared is it really feasible with case statement – harikamireddi Jan 26 '16 at 19:00
0

Let's say your target data set is in 'TargetTable' and your source data set is in 'SourceTable'; Perhaps you could try something like:

SELECT Name, Sal FROM TargetTable as t
WHERE (NOT EXISTS 
    (SELECT Name, Sal from SourceTable as s
     WHERE(Name = t.Name) OR (Sal = t.Sal)))
Wes Doyle
  • 2,199
  • 3
  • 17
  • 32
0

With the following assumptions:

  1. You are only comparing the sal columns for the source and target
  2. Source and target are joined based on the id primary key

The query could be written as: 

SELECT
        T1.Name, T1.sal, S1.sal
FROM
        schemaName.TargetTable T1, 
        schemaName.SourceTable S1
WHERE 
        T1.id = S1.id
        AND T1.sal <> S1.sal
jspek
  • 438
  • 3
  • 10