Re: Help with 4gl !!!

John Cokos (jcokos@ix.netcom.com) wrote: : Hello all, : I am having a BIG problem with 4gl. Our company hired a firm to do : some programmnig for us. We are being told that something we had : previously agreed upon is not possible and I would like a bit ov : clarification from the : experts..... : Given two tables: : 1. Customers which contains approx 1 Million records and is : indexed on the Telephone Field and : the Customer field and NOT on the Company Field : 2. Business which contains approx 50,000 records and IS indexed : on the Company field : And the following information: : 1. Every Company in the Customer Table has a correspondong : record in the Business Table : 2. Every time a Business is appended to the Customer Table, the : appropriate info is duplicated : in the Business Table (Company, Contact Person, Phone#) : Is it possible, via 4gl to accept as input a company name (allowing for : wildcards), use that to look up the contact person & phone Number in : the Business Table. If there are multiple matches, show a Pick-List. : When one : from this list is selected, use the Phone Number (From Business) to : search the Customer Table and display all of the fields from the : Customer Table ?? : This does not seem to be too difficult in theory, yet we are being told : that the only solution is to place an index on the Conpany field in : Customer Table (Which Will Slow down many other programs) or to write : down the ph : one number it finds in Business Table and then type that into the : Customer Table (Too Many Seps for data entry) If anyone has an answer : and possibly some sample code to make this possible, I would greatly : appreciate it. : : Thank you, : John Cokos : jcokos@ix.netcom.com John, It is realy easy to do what you want. 1. Every Company in the Customer Table has a correspondong record in the Business Table - Try following SQL SELECT UNIQUE company from customer WHERE EXISTS (SELECT company from business WHERE customer.company = business.company) Ofcause you can gain perfomance if you have an index for cutomer.company 2. You can use wild cards to select on any of the fields if the screen by executing a constructed query dynamically - CONSTRUCT --> PREPARE -> EXECUTE The reult from the dynamic query can be send to an a pop-up with an array to select from a pick-list. I cant come up with an exact code for this without more details on this. If you want further help please email me. Good Luck! Dhammika ------------------------------------------------------------------------------ Dhammika Weerasekera Mincom Pty Ltd, DBA Wyandra Street, | | | | | | dhammika@mincom.oz.au Teneriffe, Brisbane, | | | | | | Phone: +61 7 3303 3072 X2072 Queensland, | | | | | | Fax: +61 7 3303 3232 Australia 4005 |M |I |N |C |O |M ------------------------------------------------------------------------------