Hello,

Could some one pleaseexplain how I would modify a date with webfocus to return a date from yesterday or the day before. Basically 2days before today. I need it to DEFAULT to yesterday, but if the date field has no date from yesterday then show the day before or the last date.

Thank you,

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

Is there a sysdate, like in Oracle, in Webfocus or a way do a compare/subtract against todays date.

pls check this out ..:\ibi\DevStudioXX\srvXX\home\doc\dmfunc.chm

QuickLearner, you have a lot of reading to do. Of course there's a system date, the date can be retrieved in many formats. And there are functions to subtract units from dates - days, months, years, hours, minutes, seconds, etc. A quick search on in this forum will yield the answers to your questions. Even better, get hold of the documentation - it's invaluable.

http://documentation.informati...ilders.com/wf76x.asp

If you use Developer Studio, look into the Help facility - it has all the important documentation at your fingertips.

Use this In Define Section-

DEFINE FILE TABLE1
YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ;
LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ;
FDATEYST/A8YYMD=YST ;
FDATELST2/A8YYMD=LST2 ;
END

I love all you guys!! I'd rather get a DIRECT answer from you intelligent guys.

after a "quick" read &MYSTAMP seems to be the sysdate. I have implementated the following code. Please advise

  -SET &MYSTAMP = &YYMD;


WHERE APPROVAL_DATE GE &MYSTAMP-2;
WHERE APPROVAL_DATE LE &MYSTAMP;

Can this WHERE statement be done...Do I need to reformat APPROVAL DATE. Approval date is coming from ORACLE as 2010/07/28 13:41:00. Basically I wanna retrieve all information where APPROVAL DATE IS LESS THAN TODAY BUT GREATER THAN 2 DAYS AGO.

quote:

Use this In Define Section-

DEFINE FILE TABLE1
YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ;
LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ;
FDATEYST/A8YYMD=YST ;
FDATELST2/A8YYMD=LST2 ;
END

I don't have a DEFINE FILE a the moment, so I will create one. Does the name matter. I see you have TABLE1. the date is coming from an ORACLE TABLE name in webfocus as OFFNSDAT. does this need to be the name of the DEFINE FILE also or it doesn't matter.

first you need an EVAL after the system date setting;
-SET &MYSTAMP = &YYMD.EVAL ;
then you need to read up on the function (in your Using Functions manual, the bible) AYMD
That function adds a number of days to a date.
to Subtract, you add a negative number of days.
Go download the Using Functions manual . it will make you happy.

Now, having said that, with Oracle, you may have to DEFINE a variable to get your &YESTERDAY into a DATE-TIME format. Again, there's a whole chapter in that manual on Date-Times.

UPDATED CODE:

this gives a blank screen

  
DEFINE FILE OFFNSDAT
YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ;
LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ;
FDATEYST/A8YYMD=YST ;
FDATELST2/A8YYMD=LST2 ;
END


TABLE FILE  OFFNSDAT
HEADING CENTER
"&SUB_ASG"

PRINT UPLASTNAME AS ''

APPROVAL_DATE

BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';

WHERE APPROVAL_DATE EQ FDATEYST;
WHERE APPROVAL_DATE EQ FDATELST2;

-*ON TABLE SET EMPTYREPORT ON
ON TABLE HOLD FORMAT HTML AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

I mean this with the uptmost respect. I'm not really trying to read that book. My boss has me on a tight deadline to complete this project. and I would like to just "make this work" at the moment. If anyone could advise me after reviewing my code that would be much appreciated.I know all ya'll can empathize with my story.

quote:

Originally posted by QuickLearner:

<snip>
WHERE APPROVAL_DATE EQ FDATEYST;
WHERE APPROVAL_DATE EQ FDATELST2;
<snip>

Since those are different dates (1 and 2 days ago), APPROVAL_DATE cannot very well be EQual to both, can it?

Try
WHERE APPROVAL_DATE LE FDATEYST AND APPROVAL_DATE GE FDATELST2;

quote:

Originally posted by QuickLearner:


WHERE APPROVAL_DATE EQ FDATEYST;
WHERE APPROVAL_DATE EQ FDATELST2;

Your absolutely right. But when I make those changes now I get a BLANK SCREEN. Please see below for code:

 -* File District_Org_Chart.fex
-SET &ECHO=ALL;
-*
-*-DEFAULT &DIVISION = '1D' ;
-* DEFAULT &SUB_ASG = 'SHIFT 1' ;


ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR
SQL SQLORA
select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER, 
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE, 
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE, 
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID, 
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES 
where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and 
INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and 
EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and 
INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and
INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and
INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND
offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A'
and ROWNUM <= 10;


-*TABLE FILE SQLOUT
-*PRINT *
-*ON TABLE HOLD FORMAT ALPHA
-*END

TABLE 
ON TABLE HOLD AS OFFNSDAT
END

-*RUN


-*EXIT

-RUN

TABLE FILE OFFNSDAT
SUM
INCIDENT_SUPPLEMENTS.APPROVAL_DATE 
INCIDENTS.INC_REPORT_NUMBER 
INCIDENT_SUPPLEMENTS.CREATOR_ID 
INCIDENT_TYPE_CODES.DESCRIPTION 
OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE 
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE 
INCIDENTS.INCIDENT_ID 
INCIDENTS.ITC_CODE 
OFFENSES.INC_INCIDENT_ID 
EMPLOYEES.LOGIN_ID 
EMP_SERVICE_HISTORIES.EJS_EMP_ID 
EMPLOYEES.EJS_EMP_ID 
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE
BY OFFICER_ID
ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID

-RUN

-*?FF HOLD
-*EXIT

-*JOIN PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1
-*JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1

-*-SET &MYSTAMP = &YYMD;

JOIN OFFICER_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTER AS J1

DEFINE FILE OFFNSDAT
YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ;
LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ;
FDATEYST/A8YYMD=YST ;
FDATELST2/A8YYMD=LST2 ;
END


TABLE FILE  OFFNSDAT
HEADING CENTER
"&SUB_ASG"

PRINT UPLASTNAME AS ''

APPROVAL_DATE

BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';

WHERE APPROVAL_DATE LE FDATEYST;
WHERE APPROVAL_DATE GE FDATELST2;

-*ON TABLE SET EMPTYREPORT ON
ON TABLE HOLD FORMAT HTML AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = overtime_blue.sty,
	 TOPMARGIN=0.0,
	 LEFTMARGIN=0.0,
	 RIGHTMARGIN=0.0,
	 BOTTOMMARGIN=0.0,
$
TYPE=HEADING,
     STYLE=BOLD,
$
TYPE=REPORT,
  BACKCOLOR=RGB(153 204 255),
  FONT=ARIAL,
$
TYPE=REPORT
    COLUMN=3,
	WIDTH=1.2,
	WRAP=1.2,
	SIZE=9,
$
END

-HTMLFORM BEGIN
<html>
<body style="background-color:#99CCFF;")>
<div valign="top" style="width:150px;">
!IBI.FIL.X1;
</div>
</body>
</html>
-HTMLFORM END

 

quote:

ON TABLE HOLD FORMAT HTML AS 'X1'

Make that
... FORMAT HTMTABLE ...

I did a ?FF and found out that my APPROVAL_DATE is being seen as HYYMDS. I need to change this to YYMD to due subtraction/addition. Could someone please advise.

Thank you

quote:

APPROVAL_DATE

APPROVAL_DATE1/YYMD = HDATE
(APPROVAL_DATE,'YYMD');

quote:

APPROVAL_DATE1/YYMD = HDATE
(APPROVAL_DATE,'YYMD');

Is this suppose to be 1 line: as

APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD');

quote:

APPROVAL_DATE1/YYMD = HDATE
(APPROVAL_DATE,'YYMD');

thank you

The more I learn this software, the more I'm starting to like it.

The HDATE works perfectly and converts my APPROVAL DATE. But once I have APPROVAL_DATE EQ FDATEYST. PAGE GOES BLANK. ARE THOSE TWO FUNCTIONS YST AND LST2 CORRECT. Please review below code. Thank you:

 -* File District_Org_Chart.fex
-SET &ECHO=ALL;
-*
-*-DEFAULT &DIVISION = '1D' ;
-* DEFAULT &SUB_ASG = 'SHIFT 1' ;


ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR
SQL SQLORA
select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER, 
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE, 
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE, 
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID, 
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES 
where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and 
INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and 
EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and 
INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and
INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and
INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND
offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A'
and ROWNUM <= 10;


-*TABLE FILE SQLOUT
-*PRINT *
-*ON TABLE HOLD FORMAT ALPHA
-*END

TABLE 
ON TABLE HOLD AS OFFNSDAT
END

-*RUN


-*EXIT

-RUN

-*?FF HOLD
-*-EXIT

TABLE FILE OFFNSDAT
SUM
INCIDENT_SUPPLEMENTS.APPROVAL_DATE 
INCIDENTS.INC_REPORT_NUMBER 
INCIDENT_SUPPLEMENTS.CREATOR_ID 
INCIDENT_TYPE_CODES.DESCRIPTION 
OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE 
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE 
INCIDENTS.INCIDENT_ID 
INCIDENTS.ITC_CODE 
OFFENSES.INC_INCIDENT_ID 
EMPLOYEES.LOGIN_ID 
EMP_SERVICE_HISTORIES.EJS_EMP_ID 
EMPLOYEES.EJS_EMP_ID 
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE
BY OFFICER_ID
ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID

-RUN




-*JOIN PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1
-*JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1

-*-SET &MYSTAMP = &YYMD;

JOIN OFFICER_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTER AS J1

DEFINE FILE OFFNSDAT
APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD'); 
YST/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -1 ) ) , 'YYMD' , 'I8YYMD' ) ;
LST2/YYMD=DATECVT ( ( DATEADD ( ( DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ) , 'D' , -2 ) ) , 'YYMD' , 'I8YYMD' ) ;
FDATEYST/A18YYMD=YST ;
FDATELST2/A18YYMD=LST2 ;

END



TABLE FILE  OFFNSDAT
HEADING CENTER
"&SUB_ASG"

PRINT UPLASTNAME AS ''

APPROVAL_DATE1

BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';

//Updated to APPROVAL_DATE1

WHERE APPROVAL_DATE1 EQ FDATEYST;

-*WHERE APPROVAL_DATE EQ FDATEYST;
-*WHERE APPROVAL_DATE GE FDATELST2;

-*ON TABLE SET EMPTYREPORT ON
-*ON TABLE HOLD FORMAT HTML AS 'X1'
ON TABLE HOLD FORMAT HTMTABLE AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = overtime_blue.sty,
	 TOPMARGIN=0.0,
	 LEFTMARGIN=0.0,
	 RIGHTMARGIN=0.0,
	 BOTTOMMARGIN=0.0, 

There are a couple of 'rules' when reporting. The first is to do no more work than is necessary.

So you are retrieving all dates, then screening for only 2 of these.

Taking a step back, where there are dates that are calculated outside the data, then these dates can, and should, be done using Dialogue Manager variables.

APPROVAL_DATE is a datetime field, so rather than converting that, keep it as is and use datetime values in &variables:

-SET &HI_DATE = &YYMD | ' 00:00:00';
-TYPE &HI_DATE
 
-SET &LO_DATE = DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),'D',-2)),'YYMD','A8YYMD') | ' 00:00:00';
-TYPE &LO_DATE

(only tested on SQL server, so hope Oracle is the same).
then in the SQL add:

  WHERE INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= &LO_DATE.QUOTEDSTRING AND THISDATE < &HI_DATE.QUOTEDSTRING

Check the output.

In the TABLE section for the SQL output don't use the same name for both HOLD and then HOLD FORMAT FOCUS. Keep these names unique so that there can be no confusion.

For the JOIN, I would use:

JOIN OFFICER_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTERAS J1

So there would be no requirement for an INDEX on OFFICER_ID.

Earlier you posted another thread about the JOIN. In there you pasted the act file instead of the max file. This is only to check that the field format of PerID and OFFICER_ID are equal and can be JOINed effectively.

And whilst you are under pressure to produce results, you really need to spend some time training. Your report is relatively simple and should be able to be developed and debugged within a very short time frame. WebFOCUS is huge and some things simple, but some are not so straightforward and you need to understand what WebFOCUS is doing. To develop effectively the training is essential, and all on this Forum will always help, but only if you help yourself also.

I read and was able to find a easier way to subtract dates, Is this correct though. It does seem to be working:



APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD');
MINUSONEDATE/I8YYMD=&YYMD-1;
MINUSTWODATE/I8YYMD=&YYMD-2;

NO!

If the date is 20140201, then you will end up with 20140200 and 20140199.

This is why we use date functions.

I do not want to do a WHERE CAUSE. Because this field APPROVAL_DATE is tied in with another colunm based on the Join. So if the where clause is not met no data is shown. I would like to create a SET field to do the validation before the PRINT OUT. So I have created the below, please review:

I believe _FOC_NULL IS NULL in the webfocus software. So if it equal no just show the '', I would like to also say if it does not meet the 1 day to 2 day criteria show '' as well.

-SET &DISDATA = IF APPROVAL_DATE1 EQ _FOC_NULL THEN ''
ELSE APPROVAL_DATE1 ;