Re: creating unique text values?

From: Anatoly Moskovsky <avm_at_trais.com.ua>
Date: Sun, 26 Aug 2001 16:07:11 +0300
Message-ID: <TRS11125DD87@trais.com.ua>

Hi!

• On august 26 Sebastian Scholz wrote:

 SS> Now, I would like to make each Value unique by simply adding Spaces
 SS> to the right as needed.
 SS> So it should look like:

 SS> A ------
 SS> B
 SS> B_
 SS> B__
 SS> B___
 SS> E

 SS> E_
 SS> G

 SS> Spaces are marked with '_'.

 SS> I tried:

 SS> UPDATE TEST a SET a.A = RPAD(a.A, (LENGTH(a.A)+  SS> (SELECT COUNT(*) FROM TEST b WHERE b.A=a.A) - 1) );

 SS> but get:

 SS> Error in Line2:

 SS> ORA-00904: invalid column name

 SS> 1. Why do I get this Error and
 SS> 2. Is there another Solution for this?

You probably use an old Oracle version. This shouldn't raise an error. But any way this wouldn't work because an UPDATE does not see the changes it makes.

Try something like this:

declare
padn number;
laststr varchar(255);
begin

    for c in (select rowid, a from test order by a)     loop

        if laststr is null or laststr <> c.a then
           laststr := c.a; padn := 0;
        end if;
        update test set a = a || rpad(' ',padn)
            where rowid = c.rowid;
        padn := padn + 1;

    end loop;
end;
/

Bye

---

Anatoly Moskovsky, Oracle Developer avm_at_sqlbatch.com [SQL Batch/Oracle, DBA tools, reverse engineering, scripting extensions]: http://sqlbatch.com/sb/ Received on Sun Aug 26 2001 - 08:07:11 CDT