Expert: Jean-Pierre Zuate - 11/25/2008

Question
Sir/Madam

   I have huge database.  In that I have a table 'RPLI_DET' columns 'POLICY_NO' and 'KEY_ID'.  In policy_no column I wanted to rectify the uniqueness.
Ex.If a policy_no='R-KT-SK-EA-110001' there will be the same policy_no like'R/KT/SK/EA/110001' or 'R/KT-SK-EA-110001'.  I wanted to know how many policy_no are there as above in rpli_det table.  Matter is very much urgent.  I expect your suggestion as early as possible.
Smt.Indiramni
SA RPLI S K Region
Bangalore-01  

Answer
Hello,

I don't know if Oracle have particular functions to do that (remember I'm an Ingres expert not an Oracle expert) but if I have to do that i will do something like
SELECT DISTINCT
substr(policy_no,1,1), substr(policy_no,3,2), etc,
count(*)
FROM
RPLI_DET
GROUP BY policy_no

If result is not what you expect you can put the result of this select in a transition table (create table ... as select) and then count the rows of this new table.

You problem, If I understant it right, is to eliminate the "-", "/" (and so on) before counting something.

Hope this help you.

Regards,
Jean-Pierre ZUATE
http://lafageconseil.fr