Re: more conversion issues.

[Chris Travers <..hidden..>]

On Thu, Dec 1, 2011 at 6:34 PM, Alvin Starr <..hidden..> wrote:
>
> I have about 2000 duplicate AP invoice numbers.
> Which is a pain to go through and fix one at a time.

Right.
>
> We have a number of vendors who supply invoices monthly with a common
> account number.
>
> Does anybody have a process suggestion that I can use to make all of
> these unique.
> I was thinking of using the existing invnumber + id but that will be
> somewhat ugly.
>
> Also moving forward how to others deal with this issue?

what I have done is use an update and subselect.

So something like (please test this before using it!):

UPDATE ap SET invnumber = eca.meta_number || ':' || invnumber
    FROM entity_credit_account eca
  WHERE eca.id = ap.entity_credit_account
               AND invnumber IN (select invnumber from ap
                                       group by invnumber
                                           having count > 1);

This could probably be done using a common table expression at least
in 9.1 but don't has as much experience with that format.

Best Wishes,
Chris Travers

>
> --
> Alvin Starr                   ||   voice: (416)585-9971x690
> Interlink Connectivity        ||   fax:   (416)585-9974
> ..hidden..              ||
>
>
>
> ------------------------------------------------------------------------------
> All the data continuously generated in your IT infrastructure
> contains a definitive record of customers, application performance,
> security threats, fraudulent activity, and more. Splunk takes this
> data and makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> Ledger-smb-users mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users