This problem is usually caused by duplicate row checks due to a bad Primary Index on a SET table.
What's the actual data in CDD.DT_ID?
Thanks Dieter. you are correct bcoz the issue is with bad primary index.
am using CDD.DT_ID,DTD.DB_TBL_ID two index fields and i have created index only on DT_ID while creating volatile table.
i have modified the volatile table creation statement (PRIMARY INDEX ( DT_ID,DB_TBL_ID)) and now its working fine.
i have tested it in SQL assisant and need to run through Bteq script. will let you know once testing is done.