|
----- Original Message ----- From: "Mark Allen" <allenmark@nu-z.net> To: <midrange-l@midrange.com> Sent: Tuesday, April 02, 2002 8:20 AM Subject: SQL or Logical file equivalent for this code We do lots of queries to generate name and address files for various marketing and informational purposes. I then end up running the final generated output file thru a program with the following code to "fix" the name field. Is there a way to accomplish the above using either SQL or in a logical file. or is there a better way than what I am doing now? [bruce] Ok, I can't find my coffee cup... it's around here somewhere.... anyway, based on the comment about doing lots of queries, I would be tempted to do the following: -Take the key and the name, create a new physical file with the name in the desired format. -Take that code and create trigger programs for add, change, delete to maintain the field in the second physical. -For queries, use a join between the files. You can also create a join file ahead of time and direct the query writers to use that file. The only other thought I had (and I would be hard pressed to really do this) is creating a UDF that implements the code snippet and then (in SQL) you can request the name field returned through that function. This would not fix the query issue though and might represent a performance problem. A view could then be created based on the UDF value returned for the name field and that view could be queried, but again, there is a performance cost here. The join would probably represent less of a performance issue than the UDF. Of course, if you can modify the original file and add the field there, then the join is removed, but then you basically have broken 1st NF. Not that that might matter. <VBG> =========================================================== R. Bruce Hoffman, Jr. -- IBM Certified Specialist - iSeries Administrator -- IBM Certified Specialist - RPG IV Developer "Suppose you were an idiot... And suppose you were a member of Congress... But I repeat myself." - Mark Twain
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.