Uid Upload_seq Doc_name
1 1 Test_Result.pdf
1 2 Test_Result.pdf
1 3 Test_Result.pdf
1 4 Test_Result_1.pdf
1 5 Test_Result_2.pdf
1 6 Test_Result_01.pdf
1 7 Test_Result_001.pdf
1 8 Test_Result1.pdf
1 9 Test_Result.pdf
1 10 Test_Result.pdf
1 11 Test_Result.pdf
1 12 Test_Result.pdf
Our Application allows a user to attach multiple documents with the same name.
Now, The time came when the document has to be extracted into file systems.
Knowing that a directory can have only unique document, which results in either losing the document or renaming them
Business doesn't want to loose any document.
They came to decide to rename it.
But suffixing "_n" where n is number something like 1, 001 or 000001
After applying logic, I faced a conflict that the document which I renamed is matched with another document with same name.
Uid Upload_seq Modified_doc_name
1 1 Test_Result.pdf
1 2 Test_Result_1.pdf -- Conflict
1 3 Test_Result_2.pdf -- Conflict
1 4 Test_Result_1.pdf
1 5 Test_Result_2.pdf
I am using
CASE WHEN COUNT(1) OVER (PARTITION BY uid,Doc_name)>1
THEN Doc_name||'_'||rownum
ELSE WD.FILENAME
END as Modified_filename
Can you help me out in resolving the conflict