Martin Schlesier Martin Schlesier - 3 months ago 7
SQL Question

SQL to analyze data after migration

I have a lot of data migrated and now wants to check them for correctness.

Below is a simple example:
I have migrated a table which was already filled in another.
Now I want to check the number of both tables and compare.
In addition I have created the following query:

SELECT 'Prüfung1' AS "Prüfung", (CASE
WHEN (SELECT count(*) FROM TableA) = (SELECT count(*) FROM TableB)
THEN 'OK!'
ELSE '!!! Anzahl stimmt nicht überein !!!' END) "Ergebnis" FROM dual


However, I would carry out several such tests, and show as one result. If I will be replacements in a second test this appears as a column rather than a row.

SELECT * FROM (
SELECT 'Prüfung1' AS "Prüfung", (CASE
WHEN (SELECT count(*) FROM TableA) = (SELECT count(*) FROM TableB)
THEN 'OK!'
ELSE '!!! Anzahl stimmt nicht überein !!!' END) "Ergebnis" FROM dual) check1,
(
SELECT 'Prüfung2' AS "Prüfung", (CASE
WHEN (SELECT count(*) FROM TableC) = (SELECT count(*) FROM TableD)
THEN 'OK!'
ELSE '!!! Anzahl stimmt nicht überein !!!' END) "Ergebnis" FROM dual) check2;


My Result should be:


|Tests|Result|
|Check1|Result1|
|Check2|Result2|
|Check3|Result3|
...


How can I do that best?

Sorry for my englsih! ;-)

Update for @vercelli

Here the query with the ORA-00904 Error:

with myTables as (select 'tableA' as tableName, count(*) as howMany from MIGRATION_OLDTABLE1 union all
select 'tableB' as tableName, count(*) as howMany from OLDTABLE1 union all
select 'tableC' as tableName, count(*) as howMany from MIGRATION_OLDTABLE2 union all
select 'tableD' as tableName, count(*) as howMany from OLDTABLE2
--....
--select 'tableN' as tableName, count(*) from TableC
),
myRelations (select 'tableA' as newTable, 'tableB' as oldtable from dual union all
select 'tableC' as newTable, 'tableD' as oldtable from dual )
select DECODE(a1.howMany, a2.howMany, 'OK', 'KO') as results , r.newTable ||' : ' || to_char(a1.howMany) ||' vs. '|| r.oldTable || ' : ' || to_char(a2.howMany) diff
from myTables a1 join myRelations r on a1.tableName = r.newTable
join myTables a2 on a2.tableName = r.oldTable;

Answer

I'd create a subquery with the count(*) of all the tables with their tableName. Another subquery with all the relations (newTable vs. oldTable) Then join them and compare the count(*).

with myTables as (select 'tableA' as tableName, count(*) as howMany from tableA union all
                  select 'tableB' as tableName, count(*) as howMany from tableB union all
                  ....
                  select 'tableN' as tableName, count(*) from tableN),
    myRelations as (select 'tableA' as newTable, 'tableB' as oldtable from dual union all
                 select 'tableC' as newTable, 'tableD' as oldtable from dual )
select DECODE(a1.howMany, a2.howMany, 'OK', 'KO') as results , r.newTable ||' : ' || to_char(a1.howMany) ||' vs. '|| r.oldTable || ' : ' || to_char(a2.howMany) diff
  from myTables a1 join myRelations r on a1.tableName = r.newTable
                   join myTables a2 on a2.tableName = r.oldTable
  ;

SAMPLE OUTPUT

results diff
  KO    tableA : 5624 vs. tableB: 5476
  OK    tableC : 1576 vs. tableD: 1576
Comments