ALBERT,
It's slllooooowwwwlly sinking in. I love the way you accomplished this. As
a novice, I certainly wouldn't have thought of this...live and learn as they
say !
Anyway, I had some questions I need to ask to clarify my understanding:
1. You referred to TheTable and ThisTable in the code and comments....please
confirm that you are talking about the same table.
2. CREATE INDEX RefGroups....was this meant to be the exact same name as the
RefGroups table you previously created ? Or was it supposed to be something
like RefGroups1 ?
3. Please confirm you have a total of two tables you are using: ThisTable
and RefGroups.
4. ThisTable is supposed to represent my current table containing the
columns I described ?
Thanks.
Jay
----- Original Message -----
From: "Albert Berry" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Sunday, February 24, 2008 4:58 PM
Subject: [RBASE-L] - Re: SQL Question
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
