I really tried and googled my a*s off.. i found "some" kind of solution but it's still not the perfect solution because i still need excel to finish it.
Situation:
We migrate from table1 to a new table (table2) which should store the same data. I want to make sure that all the data from table1 is 100% the same as in table2. table2 also has additional columns which table1 not have and this is fine. Its all about be sure that the ones from table1 are in table2 and comparing them. The identifier is FIELD_0 which are in both tables and are having the same number.
Problem: the data comes like this select * from table1;
Goal: Compare table1 with table2 transpose the columns to one column as the first column and then show the record from table1 Vs. table2 --> doesn't matter if they match or not. for that I would like to have a function like exact in excel. So it looks then like this: output goal
I tried minus, union all, joins, pivot etc. Cant bring it to the view like above.
CodePudding user response：
WITH
tb_1 AS
(
SELECT
CAST(123 as VARCHAR2(20)) "F0",
CAST(4772119111 as VARCHAR2(20)) "F1",
CAST('02.05.2022 13:22' as VARCHAR2(20)) "F2",
CAST('2101968625233' as VARCHAR2(20)) "F3",
CAST(Nvl(Null, 'NULL') as VARCHAR2(20)) "F4",
CAST('996013148260662053' as VARCHAR2(20)) "F5",
CAST(Nvl(Null, 'NULL') as VARCHAR2(20)) "F6",
CAST(2 as VARCHAR2(20)) "F7",
CAST(0 as VARCHAR2(20)) "F8",
CAST('02.05.2022 11:42' as VARCHAR2(20)) "F9",
CAST('02.05.2022 13:21' as VARCHAR2(20)) "F10",
CAST(Nvl(Null, 'NULL') as VARCHAR2(20)) "F11",
CAST('HELLO WORLD' as VARCHAR2(20)) "F12"
FROM DUAL
),
tb_2 AS
(
SELECT
CAST(123 as VARCHAR2(20)) "F0",
CAST(334651561 as VARCHAR2(20)) "F1",
CAST('02.05.2022 13:21' as VARCHAR2(20)) "F2",
CAST('2101968625233' as VARCHAR2(20)) "F3",
CAST(Nvl(Null, 'NULL') as VARCHAR2(20)) "F4",
CAST('996013148260662053' as VARCHAR2(20)) "F5",
CAST(Nvl(Null, 'NULL') as VARCHAR2(20)) "F6",
CAST(2 as VARCHAR2(20)) "F7",
CAST(Nvl(Null, 'NULL') as VARCHAR2(20)) "F8",
CAST('02.05.2022 13:25' as VARCHAR2(20)) "F9",
CAST('02.05.2022 13:21' as VARCHAR2(20)) "F10",
CAST(Nvl(Null, 'NULL') as VARCHAR2(20)) "F11",
CAST('HELLO WORLD' as VARCHAR2(20)) "F12"
FROM DUAL
),
t1_cols AS
(
SELECT
'T1' "TBL", COLS, VALS
FROM
(
SELECT
*
FROM
(
SELECT
*
FROM
tb_1
PIVOT
(
Max(F1) "F1", Max(F2) "F2", Max(F3) "F3", Max(F4) "F4", Max(F5) "F5", Max(F6) "F6",
Max(F7) "F7", Max(F8) "F8", Max(F9) "F9", Max(F10) "F10", Max(F11) "F11", Max(F12) "F12" FOR F0 IN('123' as F0)
)
)
UNPIVOT
(
VALS FOR COLS IN(F0_F1, F0_F2, F0_F3, F0_F4, F0_F5, F0_F6,
F0_F7, F0_F8, F0_F9, F0_F10, F0_F11, F0_F12)
)
)
),
t2_cols AS
(
SELECT
'T2' "TBL", COLS, VALS
FROM
(
(
SELECT
*
FROM
tb_2
PIVOT
(
Max(F1) "F1", Max(F2) "F2", Max(F3) "F3", Max(F4) "F4", Max(F5) "F5", Max(F6) "F6",
Max(F7) "F7", Max(F8) "F8", Max(F9) "F9", Max(F10) "F10", Max(F11) "F11", Max(F12) "F12" FOR F0 IN('123' as F0)
)
)
UNPIVOT
(
VALS FOR COLS IN(F0_F1, F0_F2, F0_F3, F0_F4, F0_F5, F0_F6,
F0_F7, F0_F8, F0_F9, F0_F10, F0_F11, F0_F12)
)
)
)
SELECT
REPLACE(t1.COLS, 'F0_', '') "COLUMNS",
t1.VALS "TABLE1_OLD",
t2.VALS "TABLE2_NEW",
CASE WHEN t1.VALS = t2.VALS THEN 'TRUE' ELSE 'FALSE' END "COMPARISON"
FROM
t1_cols t1
INNER JOIN
t2_cols t2 ON(t2.COLS = t1.COLS)
--
-- R e s u l t
--
-- COLUMNS TABLE1_OLD TABLE2_NEW COMPARISON
-- ------- -------------------- -------------------- ----------
-- F1 4772119111 334651561 FALSE
-- F2 02.05.2022 13:22 02.05.2022 13:21 FALSE
-- F3 2101968625233 2101968625233 TRUE
-- F4 NULL NULL TRUE
-- F5 996013148260662053 996013148260662053 TRUE
-- F6 NULL NULL TRUE
-- F7 2 2 TRUE
-- F8 0 NULL FALSE
-- F9 02.05.2022 11:42 02.05.2022 13:25 FALSE
-- F10 02.05.2022 13:21 02.05.2022 13:21 TRUE
-- F11 NULL NULL TRUE
-- F12 HELLO WORLD HELLO WORLD TRUE
Here is one way to get the result you asked for. You have to transform the data types to be unique and then do pivoting and unpivoting. WITH clause for tb_1 and tb_2 are here selected from dual but you should use your own tables/columns/data. And CAST(Nvl(Null, 'NULL') as VARCHAR2(20)) is here for creating dataset. Your code should take care of null values everywhere to get the types correctly. Regards...