How to create a unique number based on two other numeric fields 1

[chriscboy]

I have a view which has the following fields:

SiteID, CompanyID, SiteAddress1, SiteAddress2 , SiteAddress3
===== ========= ===== ========== ========== ==========
76724 , 1202 ,Devonshire Square , 3- 11 Devonshire Square, London
79007 ,1000 , A house, Somewhere, London
76724 , 904 ,Devonshire Square , 3- 11 Devonshire Square, London

The view itself is made from two tables sites and companies. A site can belong to more than one customer, as above. This view is used by a web application, to import sites and companies, but unfortunately the web application is not structured to deal with more than 1 site per company. The only way (so I have been told) is to create a unique SiteID record for each company in the view.

So using the above example the view should be like

SiteID, CompanyID, SiteAddress1, SiteAddress2 , SiteAddress3
===== ========= ===== ========== ========== ==========
767241 , 1202 ,Devonshire Square , 3- 11 Devonshire Square, London
79007 ,1000 , A house, Somewhere, London
767242 , 904 ,Devonshire Square , 3- 11 Devonshire Square, London

The question I have is there any way I can generate a unique value in the SiteID column based on the existing SiteID and CompanyID ?

 

[Mike Lewis]

Hmm. I might be missing something, but it looks like your two views are identical. Is that what you intended?

That said, if your requirements are simply to add a new column to the view, containing a concanenation of the two existing columns, you could do it like this:

CREATE SQL VIEW .... ;
AS ;
SELECT SiteID, CompanyID, SiteID + CompanyID AS NewID, ;
... other columns here .... ;
FROM .... etc.

My apologies if I've misunderstood the question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[chriscboy]

Hi Mike,

Thanks for your reply. The two views are identical in structure, but in the second view the values in SiteID are unique, whereas in the first view they are not.

I had thought of concatenating CompanyID and SiteID to form a unique id, but that won't make it unique, as the following example proves:

CompanyID,SiteID,CompanyID+SiteID
1,1,2
1,2,3 <-- same unique ids for companyid,site
2,1,3 <-- same unique ids for companyid,site
2,2,4

(If the unique fields involved were character this would be easy as the concatenation would work!)

Hope this makes some sense.

 

[Stella740pl]

chriscboy,

I think what you want is this: CompanyID*100+SiteID

CompanyID,SiteID,CompanyID*100+SiteID
1, 1, 101
1, 2, 102
2, 1, 201
2, 2, 202

Now, all of them are unique.
Or, if you actually have more than one digit for each of the IDs, you can multiply not by 100, but by a higher power of 10 - say, 10^3, or 10^10, for that matter.

Of course, you can always convert the numbers into characters to concatenate (but I, personally, would prefer a numeric key): PADL(CompanyID,2,"0")+PADL(SiteID,2,"0")

CompanyID,SiteID, PADL(CompanyID,2,"0")+PADL(SiteID,2,"0")
1, 1, "0101"
1, 2, "0102"
2, 1, "0201"
2, 2, "0202"

 

[Mike Lewis]

Chriscboy,

Thanks for the clarification. I understand now what you want to achieve.

Stella has given you a couple of good suggestions, to which I can't add anything. Which one to adopt depends partly on whether the existing IDs are numeric or strings.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[chriscboy]

Stella,

The companyID would not go higher than 5 digits (99999), so would CompanyID*100000+SiteID suffice, to get a unique number?

Regards

Chris

 

[Stella740pl]

It depends.
Up to how many digits would SiteID go?

 

[chriscboy]

The SiteID will go up to six digits (999999)

 

[Stella740pl]

In this case, 10^6 would be enough to create unique keys.

companyID      siteID    companyID+10^6+siteID
====================================================
11111          222222      11111222222
12345          543210      12345543210 
12345          678912      12345678912 
11111          789789      11111789789  
    1          789789          1789789  
   11          789789         11789789  
  111          789789        111789789  
    2               6          2000006  
   22              65         22000065  
  222             654        222000654

Keep in mind that maximum size of numeric field in VFP is 20 digits, so if you ever want to go higher than that (so far you have only 5+6=11), you should consider using character combined key (as I shown in my previous post).

 

[chriscboy]

Thanks for the answer Stella. Would be nice if the maximum number would go >20 digits, because that would mean our business is doing extremely well

 

[Stella740pl]

Glad it helped and thanks for the star.

(Oh, and the title of the third column in the code box should read companyID[red]*[/red]10^6+siteID.)