Results 1 to 2 of 2

Thread: point the error

  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: point the error

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •