On 30-Jun-2016 10:29 -0500, Steinmetz, Paul wrote:
On 30-Jun-2016 10:05 -0500, Darren Strong wrote:
On 30-Jun-2016 09:19 -0500, Steinmetz, Paul wrote:
I have an AS/400 query with a hardcoded date.
The date is today's date - 6 months.
Example 1160130.
I'd like to make this date a variable.
Can this be done within the query?
<<SNIP>>
I'm assuming you're wanting to do some date arithmetic on today's
date? If so, I learned the following from the F1 screen in the
Define a column section of WRKQRY.
char(current(date)- 6 months,iso)
<<SNIP>>
Below is my query select.
ODUDATCYMD LT '1160130'
DSPOBJD to an output file results with ODUDAT Last used date
(MMDDYY) To compare an object's last use date, the ODUDAT needs to
be reformatted with the century added.
ODUDATCYMD is a result field from century year month day.
<ed: add prior result fields on which above noted is defined>
Result Fld Expression
ODUDATY substr(ODUDAT, 5, 2)
ODUDATM substr(ODUDAT, 1, 2)
ODUDATD substr(ODUDAT, 3, 2)
ODUDATCYMD ODUCEN||ODUDATY||ODUDATM||ODUDATD
<<SNIP colhdg>>
I would like to replace the hardcoded literal '1160130' with a
variable.
No comment was made with regard to the reference made to use of the
CURRENT(DATE) to enable changing from using the literal value. But if
indeed the resolution requires only that the literal be replaced with
the effect of the expression CURRENT_DATE-6 MONTHS, then the following
scripted actions should suffice. The idea is to create a calendar table
with [many] dates spanning the 100-year window [for some span of dates
for C={0,1} of CYYMMDD] that are sufficient to handle [most] dates
[though easily changed to include all dates from 1940-01-01 to
2039-12-31, if required, to be complete for that date\time-window];
because dates for objects will not be outside that window, the C for the
century indicator becomes moot. The instructions below include some
revisions to the query; hopefully the navigation and inputs that are to
be made are clear:
/* with an SQL interface: */
create table ODUDAT_cal as (
with
date_table ( mmddyy, date_val ) as
( select *
from table( values(
cast( '010187' as char(6) )
, date( '1987-01-01' )
) ) as primed
union all
select digits( zoned( varchar_format(
cast( date_val + 1 day as timestamp ), 'MMDDYY'), 6))
, date_val + 1 day
from date_table
where date_val < current_date
)
select * from date_table ) with data
; -- consider naming a perm library to create into, and a
-- future date, e.g. date'2020-01-01' vs current_date
WRKQRY /* at CL command-line */
@Work With Queries: 2=Change
@Define the Query: F21=Select All
@Specify File Selections: F9=Add File
@Specify File Selections (File); Input: ODUDAT_CAL
@Specify File Selections (Library); Input: QTEMP /* or perm lib */
Enter
Enter /* to confirm file selection */
@Specify Type of Join (Type of Join); Input: 2 /* outer join */
Enter
@Specify How to Join Files (Field); Input: T01.ODUDAT
@Specify How to Join Files (Test); Input: EQ
@Specify How to Join Files (Field); Input: T02.MMDDYY
Enter
@Define Result Fields: F9=Insert
@Define Result Fields (Field); Input: MONTHSAGO6
@Define Result Fields (Expression); Input:
char(current(date)-6 months, ISO)
Enter
@Select and Sequence Fields
Enter
@Select Records: erase the existing Field\Test\Value:
ODUDATCYMD LT '1160130'
@Select Records (Field); Input: DATE_VAL
@Select Records (Test); Input: LT
@Select Records (Field); Input: MONTHSAGO6
F3=Exit
F5=Report /* to test the effect */
-- at this point, save as is, or save with alternate name
-- to allow for further testing
Note: Because Query/400 Join implements using Join Defaults (JDFT)
for the otherwise effective LEFT OUTER JOIN [instead of NULL for
unmatched rows], the DATE value 0001-01-01 is the result for a blank
value of ODUDAT; an unused object will have that base-date, which
compares Less Than (LT) the Six-Months-Ago value established by the
MONTHSAGO6 result field.
midrange.com
