Re: SQL or Logical file equivalent for this code

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I'd go with the UDF.  And yes, it WOULD fix the Query/400 problem. Because
you can create a logical with a field based on a UDF.  Then just query
that logical file.  Read the following:
http://faq.midrange.com/data/cache/185.html


Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"R. Bruce Hoffman, Jr." <rbruceh@attglobal.net>
Sent by: midrange-l-admin@midrange.com
04/02/2002 08:35 AM
Please respond to midrange-l


        To:     <midrange-l@midrange.com>
        cc:
        Fax to:
        Subject:        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


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.