RE: SQL date comparison - a better way?

[rob@xxxxxxxxx]

Tom,

You're right about the explicit field lists.  Sorry that I wasn't clear on 
that.

You're questionable about the new fields needing the maintenance program 
recompiled.  If I add a field that converts a 6 digit date into a true 
date field, the maintenance on that could easily be handled with a 
trigger.  However, in a perfect world, the maintenance program would use a 
true date field and the trigger would convert that into the 6 digit date 
field for compatibility with the other programs.  Thus getting the right 
date and not having to use a cut off date.

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





qsrvbas@xxxxxxxxxxxx (Tom Liotta)
Sent by: midrange-l-bounces@xxxxxxxxxxxx
08/12/2003 06:30 PM
Please respond to Midrange Systems Technical Discussion
 
        To:     midrange-l@xxxxxxxxxxxx
        cc: 
        Fax to: 
        Subject:        RE: SQL date comparison - a better way?


midrange-l-request@xxxxxxxxxxxx wrote:

>   3. RE: SQL date comparison - a better way? (rob)
>
>  And, providing your applications do not directly access 
>the physical file, but only access the logical files, you could add 
fields 
>without any level checks.
>
>I've seen this technique used by a software vendor.

I've used the technique with good results although there are a couple 
items to be aware of.

Most importantly, the LFs must be compiled with explicit field lists. Add 
a new field to the PF and recompile it. The previous LFs can then be 
recompiled and will not cause level-checks as long as the field lists are 
unchanged and no significant changes were made to the original fields. New 
fields shouldn't need to be added at the end of the PF; they can be 
anywhere -- the original physical order can even change.

Also, there generally needs to be at least one program that is recompiled 
because this program is the one that does the physical updates. When new 
fields get added to a table, they ought to be accounted for in the primary 
physical maintenance program. This program would always need to be 
compiled over the actual PF. While it's certainly possible to have an LF 
defined for this purpose, the problem remains the same. If data in the new 
fields will be maintained, the update program needs to include the fields. 
Ideally, this will only be a single program (or module or service program 
procedure or...). In a lot of circumstances, this might be minimized by 
using default values for the new fields.

Tom Liotta

-- 
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788 x313
Fax    253-872-7904
http://www.powertechgroup.com