This content has been marked as final. Show 3 replies
Don't you have some unique ID column in the table?
In which case, all you have to do is add a PARTITION clause :
If you don't have such column, you can also compute one on-the-fly :
... MODEL PARTITION BY (id) DIMENSION BY (0 dim) ...
... MODEL PARTITION BY (rownum rn) DIMENSION BY (0 dim) ...
If i need to work on 2 columns ...how do i modify the below code...
Edited by: Krux_rap on Nov 19, 2012 1:40 PM
SELECT BIO, RTRIM (str_new, ';') new_str FROM db_temp MODEL partition by (rownum rn) DIMENSION BY (0 dim) MEASURES (BIO, BIO || ';' STR_NEW) RULES ITERATE (1000) UNTIL (STR_NEW = PREVIOUS (STR_NEW)) (str_new  = REGEXP_REPLACE (str_new, '(^|;)([^;]+;)(.*?;)?\2+', '\1\2\3'));