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
