Hi there,

I need to check if the source table has been converted correctly.

If the source has the format as:

ID Col1 Col2 Col3
1 A1 A2 A3
2 B1 B2 B3
...

And the target has the same format, but we need to apply the transformation rule on Col1, Col2 and Col3, so it will be

ID T-Col1 T-Col2 T-Col3
1 F1(A1) F2(A2) F3(A3)
2 F1(B1) F2(B2) F3(B3)


Currently I am doing sql query to report the mismatch:

select *
from source a, target b
where a.id = b.id
and
( b.T-Col1 <> case when ... (Apply Function F1 here) end
or b.T-Col2 <> case when ... (Apply Function F2 here) end
or b.T-Col3 <> case when ... (Apply Function F3 here) end
)

I will get the mismatch rows, but I cannot tell right away which column has the wrong value, so I try to do something like

select case when b.t-col1 <> ... then 'Col1 has error'
when b.t-col2 <> ... then 'col2 has error'
...

But since the Function part are quite complicated, I wonder if there is any way I don't need to repeat it in the select section? I try to add "As Col1CompareValue" in the where section, like:

select case when b.t-col1 <> Col1compareValue then 'Col1 has error'
...
end
from source a, target b
where a.id = b.id
and
( b.T-Col1 <> case when ... (Apply Function F1 here) end As Col1CompareValue
or b.T-Col2 <> case when ... (Apply Function F2 here) end
or b.T-Col3 <> case when ... (Apply Function F3 here) end
)

but it give me an error.

Any ideas are appreciated.

I can think of two ways to do this. First, just write the query like this:

select s.id,
case when (f1(s.col1) <> t.t-col1) then 'col 1 error' else '' end as col1_status,
case when (f2(s.col2) <> t.t-col2) then 'col 2 error' else '' end as col2_status,
case when (f3(s.col3) <> t.t-col3) then 'col 3 error' else '' end as col3_status
from source as s
inner join target as t on (s.id = t.id)
where f1(s.col1) <> t.t-col1 or f2(s.col2) <> t.t-col2 or f3(s.col3) <> t.t-col3

Yes it can be bulky if the functions are complex, but it gets the job done.

The other way is to create a view on the source table to look like the target table using the transform functions:

create view source_transformed as select id,f1(col1) as t_col1,f2(col2) as t_col2,f3(col3) as t_col3 from source

Then your query gets simple:

select s.id,
case when (s.t_col1 <> t.t-col1) then 'col 1 error' else '' end as col1_status,
case when (s.t_col2 <> t.t-col2) then 'col 2 error' else '' end as col2_status,
case when (s.t_col3 <> t.t-col3) then 'col 3 error' else '' end as col3_status
from source_transformed as s
inner join target as t on (s.id = t.id)
where s.t_col1 <> t.t-col1 or s.t_col2 <> t.t-col2 or s.t_col3 <> t.t-col3

HTH
Andy