Update the value of a Select AS field in a query

[miketheitguy]

Ok guys, I'm writing a query to bring together a number of different fields and values and append them all to a single table.

My query looks like this.

TableA TableB TableC TableD
Field1 Field1 Field1 Field1
Field2
Field3

Insert Into TableE (Cuid, Tablea!field1, Tablea!field2, tablea!field3, tableb!field1, tablec!field1, tabled!field1)
SELECT RandomSSR("##HHNN#HN#HN#HN") AS Cuid, tablea!field1 as field1 (etc. etc.)

Field1 in tables b,c,d and Autonumber ID's fields and they work and roll forward fine. TableA is the actual transactional data that I am assigning these ID's to. CUID as you can guess is a GUID type field. I'm writing a back end to a failing and miserable SQL and VFP 5.0 system that we have and to insert transactions into it I need a CUID field that is alphanumeric and 15 characters in length. The function RandomSSR works fine for the first record, it brings up a good random set of chars. But then it replicates that same set throughought the remaining fields. Is there any way to get the query to call the procedure anew for each record that exists?

BTW the RandomSSR code is as follows.. and no.. it's not mine. Any help or insight on this whatsoever will be greatly appreciated!

Mike The Frazzled IT Guy

------------------- CODE SAMPLE FOLLOWS --------------------

Option Compare Database
Option Explicit

Public Function RandomSSR(ByVal mask As String) As String

Dim i As Integer
Dim acode As Integer
Dim options As String
Dim char As String

' initialize result with proper lenght
RandomSSR = mask

For i = 1 To Len(mask)
' get the character
char = Mid$(mask, i, 1)
Select Case char
Case "?"
char = Chr$(1 + Rnd * 127)
options = ""
Case "#"
options = "0123456789"
Case "A"
options = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
Case "N"
options = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
Case "H"
options = "0123456789ABCDEF"
Case Else
' don't modify the character
options = ""
End Select

' select a random char in the option string
If Len(options) Then
' select a random char
' note that we add an extra char, in case RND returns 1
char = Mid$(options & Right$(options, 1), 1 + Int(Rnd * Len(options) _
), 1)
End If

' insert the character in result string
Mid(RandomSSR, i, 1) = char
Next

End Function

' generate a random string
'
' the mask can contain the following special chars
' ? : any ASCII character (1-127)
' # : a digit
' A : an alphabetic char
' N : an alphanumeric char
' H : an hex char
' all other chars are taken literally

' Example: a random-generated phone number
' phone = RandomString("(###)-####-####")

 

[PHV]

Add this line near your Dim instructions:
Static seed As Long
And replace this:
char = Mid$(options & Right$(options, 1), 1 + Int(Rnd * Len(options) _
), 1)
By this:
seed = seed + 1
char = Mid$(options & Right$(options, 1), 1 + Int(Rnd(seed) * Len(options) _
), 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244