Re: SQL Date Math

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

  Numerics for dates can add value when there is little use for date 
calculations.  To limit undesirable [not necessarily invalid; e.g. to 
still allow 9999-99-99 or 0000-##-## as special] values there is the 
CHECK CONSTRAINT.  Then as noted, within a VIEW is the option to CAST a 
derivation to DATE type.  Or by using CASE can handle more dynamic 
variations than just COALESCE of the one-dimensional [within a single 
date field] NULL value.

  In the following example DateOrdr is date data type, but DateShip is 
numeric(8, 0) to allow for keying on the single [overloaded] field and 
thus providing multiple answers for the shipping /date/ business rules.

       case
        when DateShip < 0 then /* Projected Ship Date */
          char(DateOrdr - DateShip days, ISO)
        when DateShip = 0 then /* No Ship Date needed */
          'Customer Pick Up'
        when DateShip = 99999999 then  /* Backordered */
          'Back Ordered    '
        when DateShip = 88888888 then  /* Date missed */
          '*** Overdue! ***'
        when DateShip IS NULL then /* No Date assigned */
          '** Incomplete **'
        else
          substr(char(DateShip), 1, 4)||'-'||
          substr(char(DateShip), 5, 2)||'-'||
          substr(char(DateShip), 7, 2)
       end as ShipRule

  1>
  *BEFORE read means to the database, before the database has read the 
row/record.  Assuming no ability to replace the row here with something 
other than what is really in the record, then it is mostly pointless for 
most security processing, as what row is being read is unknown to the 
trigger program, just as it is to the receiving program.
  The *AFTER *READ trigger enables the trigger program to intercept 
what the program is going to see, and has the option of preventing the 
read by sending an error message to the database; e.g. in response to 
what data and what user is trying to see that data.  There is not 
however the possibility at this point to either munge or selectively 
un-munge the data in the record; e.g. no option to blank, encrypt or 
decrypt, nor compress or decompress row data.  Such actions are the 
domain of functions.

  2>
  I am almost positive Derived Field results from a Read Trigger will 
never happen.  A program compiled against a file with FLD_CHAR8 which 
has a Read Trigger casting that to a BIN(4) alternate representation of 
a date, or to a CHAR(10) representing an *ISO date character string is 
problematic; i.e. the program will not know what to do with the data. 
Similarly problematic for the bound record length changing for something 
like adding fields.  To change the data in the compiled-against format, 
almost surely a function will always be required to cast or otherwise 
change the data the program will receive; i.e. the function defines the 
data type and attributes [for a static binding].  With the capability of 
a read trigger to modify the record, I would expect support contracts 
for any [system with any] Read Triggers would have to be specially 
priced for huge dollars or go purely consulting -- but with that, the 
loss of general support to the reasonable uses/users of the function.
  Even limiting the open of such a file for purely dynamic, the 
metadata [even if scoped to the open versus per record] would require 
some method to be established where none exists -- perhaps an open 
trigger calls the read trigger with a special request to establish the 
metadata.?  Then any programs bound to the original definition should 
fail with level check irrespective of overrides to request otherwise 
with any mismatch.?
  The whole concept seems a bit off; perhaps I just do not see 
something obvious.?  Regardless....
  Presenting derived data to a program from a file is already available 
with User Defined Functions [UDF]; both table and scalar.

Regards, Chuck