Steve, You can try something like: SEL COUNT(*) INTO vrows INDIC iv2 FROM master WHERE + CustNub2 = .This_CustNub2 AND + and hub <= 999999 AND + parentcustid = '105' AND + Custnub2 IN (SELECT CustNub2 FROM customer)
This assumes that hub and parentcustid are in the master table; if not, you need to move their expression to the sub-select like: SEL COUNT(*) INTO vrows INDIC iv2 FROM master WHERE + CustNub2 = .This_CustNub2 AND + Custnub2 IN (SELECT CustNub2 FROM customer WHERE + hub <= 999999 AND parentcustid = '105') The first expression it will count records in the master table that have a custNub2 equal to the variable .ThiscustNub2, hub is less or equal to 999999, parentcustid is equal to 105 and records exist in the customer table with matching CustNub2 columns. The second expression it will count records in the master table that have a custNub2 equal to the variable .ThiscustNub2 and records exist in the customer table with matching CustNub2 columns and the matching records in the customer table have hub less or equal to 999999 and parentcustid is equal to 105. I am not sure in which table the columns hub and parentcustid are, so you might have to modify the expression to match your table/column structure. The expressions above should give you a start. Also, something I always do is verify manually (one table at a time) that the results you are getting are the correct ones; just because you get an answer, does not necessarily mean that it is the correct one. Javier, Javier Valencia, PE 913-397-9605 Home 913-915-3137 Cell 913-649-2904 FAX -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Steve Breen Sent: Saturday, December 12, 2009 6:26 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Speeding up Javier Actually I over stated the delay it is 1-3 seconds which is still and issue, but yes the HUB (Vehicle Mileage) is in master and the ParentCustID is in customer. Custnub2 is the same in master and customer. We set this_CustNub2 to make it specific to the form when we are using it for initial entry of data to the master table. Right now I am just in the initial stages of testing this, Trying to figure out how to make this happen when there is data in master and customer first. Stephen Breen CDI Services, Inc. P.O. Box 3838 South Bend, In 46680 574-271-2027 Ext 207 / 222 574-532-7445 Cell ________________________________ From: [email protected] on behalf of Javier Valencia Sent: Sat 12/12/2009 5:38 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Speeding up Steve, >>> SEL COUNT(*) INTO vrows INDIC iv2 FROM master where .this_custnub2 + in (sel custnub2 from customer where parentcustid = '105') and hub le + 999999 <<< Are you getting the correct answer? I am not sure if you want to calculate a count from Master or customer. The sub select is normally used to limit the records that match the criteria of both tables; in this case, there does not appear to be a link between the master and customer tables. I believe that you have to explicit link the tables via a column. Javier, Javier Valencia, PE 913-397-9605 Home 913-915-3137 Cell 913-649-2904 FAX -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Steve Breen Sent: Saturday, December 12, 2009 4:07 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Speeding up Please advise me if there is anyway of speeding this up. There is a 15-30 second delay Note: All Columns are indexed. Note: I am using this as a entry eep. -- Warranty.ent -- Set variable vwarranty -- Used to remind a user to Check A Spread Sheet for Customer Equipment Verification -- Major Assumption - var This_CustNub2 is set on form entry Clear var vWarranty SEL COUNT(*) INTO vrows INDIC iv2 FROM master where .this_custnub2 + in (sel custnub2 from customer where parentcustid = '105') and hub le 999999 -- IF vrows > 0 THEN SET VAR vwarranty = 'Check warranty coverage for the unit before handling Breakdown' ELSE SET VAR vwarranty = 'TEST' ENDIF RECALC VARIABLES RETURN Stephen Breen CDI Services, Inc.
