On 08-Apr-2015 08:45 -0500, broehmer@xxxxxxxxxxxxxxx wrote:
<<SNIP>>
WHERE DATE(INSERT(INSERT(CHAR(RHDATE),5,0,'-'),8,0,'-')) +
BETWEEN CURRENT DATE - 120 DAY AND CURRENT DATE +
<<SNIP>>
FWiW, a few comments about the above snippet of the SQL:
For better performance, if no derived index matching that DATE(...)
expression exists, and especially if an index exists on the column
RHDATE [conspicuously defined as a numeric data type of eight digits
with those digits represented by the date format of YYYYMMDD], convert
the special register values [as effective literal\constant values] into
the matching numeric data type; a conversion happens only once per
effective literal rather than the complex expression performed once per row:
WHERE RHDATE BETWEEN
DEC(REPLACE(CHAR(CURRENT_DATE - 120 DAYS
, ISO), '-', ''), 8 )
AND DEC(REPLACE(CHAR(CURRENT_DATE
, ISO), '-', ''), 8 )
Note: In place of the DECIMAL [aka DEC] casting scalar, there are
also the INTEGER [aka INT] and ZONED casting scalars or the CAST scalar
available; the chosen casting would be to match the data type\length of
the column RHDATE or a\the keyed access path on that data [which does
not necessarily match the data type of the column of the physical file].
So the expression will be accepted in an environment other than one
established with period as decimal separator [aka Decimal Point (DECPNT)
or (DECMPT)], adding a blank after each comma is desirable [and
mandatory if the expression might be used in a DECPNT(*COMMA)
environment; as a favor to non-USA people referencing the archives for
an apparently functional example, the author having done that already is
a bonus for them]:
WHERE DATE(INSERT(INSERT(CHAR(RHDATE), 5, 0, '-'), 8, 0, '-'))
BETWEEN CURRENT DATE - 120 DAY AND CURRENT DATE
If the dates might include values prior to year 1000, [e.g. year 1],
then use DIGITS scalar in instead of CHAR casting; note, the explicit
specification of CHAR is optional, per implicit cast that would be
applied for the first argument of the INSERT scalar. If changing to use
DIGITS, because the expression would be more dependent on the data
typing of the column named RHDATE, explicit casting to an eight-byte
representation might be required; explicit casting avoids impact to
changes to the data type or length, though changes to numeric-as-date
columns is less typical than other numerics.
midrange.com
