I need to generate unique values for set of input fields. For example if i have col1, col2, col3 in a table I need to populate unique value in col4 in another table whenever any of these col, col2, col3 get changed. I tried using HASHROW and MD5. But there will be chances to generate same value. Is there any way to generate unique values every time depending on the input fields?
will this be done as a one-time update or whenever there's a row inserted?
Can you provide more details, e.g. DDL & sample data, number of rows, is this a batch load?
I have a table T1 which have columns col1, col2, col3. I have to populate another table T2 that will have col4 the value of which should depend on the value of col1, col2, col3 in T1. Both of them are delete insert tables. So if now if I have 1,2,3 in T1, T2 will have unique value like 'abc'. After next load if T1 have values 1,2,4; T2 should be populated with another unique value like 'cdf'. I was using HASHROW function but I found it generated same value for different set of input values. MD5 will also not give 100% unique values. Is there any other option to achieve this?
you need unique but consistent values, (1,2,3) should always return the same value?
Then you should trust MD5 or SHA256/512 to return 99.999999... % unique values or use all three columns, either directly (col1, col2, col3) instead of a single col4 or (col1||'.'||col2||'.'||col3)
the SHA algorithm must be installed as a UDF (similar to MD5):