1. Having just returned from a Sunday drive followed by a Knights of
Columbus monthly meeting, I am pleased to confirm that TheTable and
ThisTable were supposed to be TheSameTable. You did not specify a table
name for the table you displayed, so I tried naming it myself. Sorry for
the confusion, and the changing table name during the example.
2. Use whatever index names may be useful to you. The index name has no
effect on anything, I just think the index should be named something
meaningful so that you know what it is for. In this case, I used
RefGroups as the name to remind ME of what I was typing.
3. Yes, just the two tables.
4. Yes, the badly named table was the example table in your original post.
Good luck with the project.
Jay K. Rao wrote:
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
--- 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.
================================================
