[SOLVED] DEFINE FILE on APP QUERY HOLD

We have an audit that logs quite a bit of stuff, but we need to create something that does a clean up after 5 days. For some reason the DEFINE FILE FILELIST below isn't adding the new column. Anyone see something wrong with the following code that would prevent NEWDATE from showing up?

-SET &THENDTTM = DATEADD(&YYMD, 'D', -5);
-SET &THENDTTM = EDIT(&THENDTTM,'9999') | '/' | EDIT(&THENDTTM,'$$$$99') | '/' | EDIT(&THENDTTM,'$$$$$$99');
-TYPE THENDTTM = &THENDTTM

APP QUERY audit_logger HOLD
-RUN

TABLEF FILE FOCAPPQ
PRINT
	DATE
	TIME
	FILENAME
ON TABLE HOLD AS FILELIST
END
-RUN

DEFINE FILE FILELIST
NEWDATE/YYMD = DATE;
END
-RUN

TABLE FILE FILELIST
PRINT *
-*WHERE NEWDATE LT '&THENDTTM';
END

This message has been edited. Last edited by: GavinL,

- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server

Posts: 578 | Registered: October 01, 2014

jfr99

Platinum Member

posted

Hide Post

Use ...

PRINT * NEWDATE
instead of ...

PRINT *

WebFocus 8.201M, Windows, App Studio

Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008

Ignored post by jfr99 posted

Show Post

GavinL

Master

posted

Hide Post

Thanks, I figured that out, but I come to the conclusion I must be overthinking this process, because it's kicking my @$$. I can have this written in 2 minutes within .NET, but using FOCUS, it's been 2 days.

- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server

Posts: 578 | Registered: October 01, 2014

Ignored post by GavinL posted

Show Post

GavinL

Master

posted

Hide Post

Date conversions are an absolute nightmare in WebFocus. That really needs to be worked on.

How exactly does

DATEADD(&YMD, 'D', -10)

Result in:

Looks like it convert it to an int and just subtracted 10 from it, unless there is 97 days in March all of a sudden.

Help clearly gives an example with use of string date.

Example: Using the Weekday Unit
If you use the weekday unit and a Saturday or Sunday is the input date, DATEADD changes the input date to Monday. The function

DATEADD('910623', 'WD', 1)

Looks like I can't use real dates:

-SET &NOW = HGETC(10, 'HYYMDm');
-SET &THENDTTM = DATEADD(&NOW, 'D', -&KEEPDAYS);

Throws errors:

(FOC281) ALPHA ARGUMENTS IN PLACE WHERE NUMERIC ARE CALLED FOR

Kind of throws out the help that states:

Syntax: How to Add or Subtract a Date Unit to or From a Date 
DATEADD(date, 'component', increment)
where:

date 
Date

Is a full component date.

- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server

Posts: 578 | Registered: October 01, 2014

Ignored post by GavinL posted

Show Post

Tony A

Expert

posted

Hide Post

quote:
Is a full component date.

Gavin,

This is the important piece, a full component date. Not a partial representation such as &YMD but a full date representation as in &YYMD.

Folks have always had trouble in getting date conversion / addition / movement functions to work and it is usually down to a misunderstanding of what the documentation states. Whether that calls for a legacy date, full component date etc.

Once it clicks then ......

Good luck

T