Re: SQL or Logical file equivalent for this code

----- 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