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.
