All Forums Database
CCSlice 36 posts Joined 03/14
27 Jun 2014
Checking for difference in Strings

I have a query in which I check between two values (Original and New) to see if there has been a change/update has been made to a record.
Essentially I am just comparing the fields:
That is,

WHEN PRFL_TBL.Original_Value != CHG_TBL.New_Value
THEN 'Segment Field Updated'
ELSE 'No changes'

However, what I am finding is that in some cases the fields compared are indeed not equal but the contents are the same:
For example the Original_Value = FKJFI, HGHER  and the New_Value = HGHER, FKJFI or a little more complex Original_Value = THRID, GHERU, KFFEL, JFIEL  and New_Value = JFIEL, GHERU, THRID, KFFEL. 
The original logic is right; however in these examples above the logic is not accurate because they are the same codes but arranged differently.  How can I check between the Original and New values to ensure that there is indeed a difference/change and not just a rearranging of the codes?
Teradata Noob.

Raja_KT 1246 posts Joined 07/09
29 Jun 2014

In my opinion, even if there is UDF to take care of  comparison, there is chance due to data entry discrepancy that space is there among  THRID, GHERU, KFFEL, JFIEL or even space in one keyword as specified example. So I suggest  to write a UDF to meet this specific requirement.
A logic of extract the fields, trim spaces, concatenate and compare, may not work, because of many comparison cases and few more permutations and combinations. It is not a good idea too since it may be time-consuming and CPU intensive. You may miss out data.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.