Hi, Jay. I think you can create your RefGroup in this manner, but see my
note below about normalization of the database:
-- Add a RefGroupID integer column to your table.
ALTER TABLE ThisTable ADD RefGroupID INTEGER
-- Create a table that will hold the data
CREATE TABLE RefGroups ( +
RefGroup ID INTEGER NOT NULL PRIMARY KEY, +
Customer TEXT (nnn), +
JobDescr TEXT (nnn), +
Contractor TEXT (nnn) )
-- Autonumber the PK
AUTONUM RefGroupID IN RefGroups USING 1,1
-- Index the columns in TheTable to speed things up
CREATE INDEX RefGroups ON ThisTable (Customer, JobDescr, Contractor)
-- Get the unique combinations into the table
INSERT INTO RefGroups (Customer, JobDescr, Contractor) +
SELECT DISTINCT Customer, JobDescr, Contractor +
FROM TheTable
-- Index the new table
CREATE INDEX RefGroups2 ON RefGroups (Customer, JobDescr, Contractor)
-- Update the new table with the Foreign key column
UPDATE ThisTable SET RefGroupID = t2.RefGroupID +
FROM ThisTable t1, RefGroups t2 +
WHERE t1.Customer = t2.Customer +
AND t1.JobDescr = t2.JobDescr +
AND t1.Contractor = t2.Contractor
NOTE: the Customer, JobDescr and Contractor should be in a table, and
only the
integer value of the Primary Key in each of the tables should be in
the RefGroups table.
This will do 2 things:
1. You only have to change the customer spelling in one place, ever
2. Your database will be much faster, linking on integers.
Good Luck!
Albert
Jay K. Rao wrote:
Novice here doing my first application....so don't laugh too loud !
I have a table with 5 columns: refnum, date, customer, job
description, contractor name. Basically, on a given date, a customer
is given 3 referrals for contractors who can do their job
description. My system assigns a unique referral number (refnum) for
each of the three referrals. As an example:
*_REFNUM DATE CUSTOMER JOB DESCR. CONTRACTOR_*
111111 2/2/08 STEVE SMITH Build a fence John's Fence Co.
111112 2/2/08 STEVE SMITH Build a fence Bob's Fence Co.
111113 2/2/08 STEVE SMITH Build a fence Gary's Fence Co.
In my application I have a need to come up with a new column (say
called REFGROUP) which will identify all referrals belonging to a
"referral group" like the one above. I'm planning on assigning the
smallest of the REFNUMs in a referral group to the REFGROUP column for
each of the referrals in the "referral group". For example, I would
want to assign 111111 to the REFGROUP column for all three of the rows
above. The idea, of course, is to be be able to know which referrals
in my database of over 200,000 referrals belong together (ie,
referrals having the same date, customer name, and job
description...only with different contractor names that were referred
by us). It is possible for a referral group to consist of only 1
referral and may contain more than 3 referrals....3 is just the
average number of referrals we give per job.
I'm writing a brand new application in 7.6. My old application is in
DOS and the original programmer didn't use a REFGROUP but I would like
to. I can certainly take care of this for all new referrals that are
given after my new application is up and running. However, for
consistency, I need to now go back and assign the REFGROUP numbers for
the 200,000 referrals that are currently in the database.
MY QUESTION: Can a clever SQL statement(s) handle this or do I need
to write a command script to get this done ? I have not been able to
think of the correct SQL statement yet so any help on this would be
appreciated. As far as a command script implementation, I can think I
can come up with some code to do this.BUT I feel like I need to be
able to use a "pointer" to the current row in a loop. I checked the
online Command documentation and didn't see any mention of such a
"pointer" for a table. Perhaps one doesn't exist. The general idea
would have been to have two loops (and two identical tables if
needed)...the outside loop's pointer pointing to row 1 and the inside
loop's pointer pointing to the various rows of the second table, doing
the comparisons, and setting REFGROUP accordingly. Again, I don't
know if such "pointers" exist or not and even if it did, perhaps my
implenation scheme is not "good use of R:BASE power" !!! ???
Would appreciate any guidance on whether my task can/should be
accomplished with an SQL statement(s) or if a more involved command
script is needed. I can then take it from there.
Thanks.
Jay
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
