I need to write a webfocus statement that basically says that if a person has a approval date that is withIN 2 days ago from today show this person. If a person does not have a approval_date that is 2 days ago show a blank. If we could put this into an variable to display to screen that would be great.

Person alias is CAD_EMP_ID
Date alias is APPROVAL_DATE

I have the algorithm below, please review. Once again, thank you for all your help!!

IF (INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= trunc(sysdate) -2 and INCIDENT_SUPPLEMENTS.APPROVAL_DATE < trunc[sysdate))
AND HAS A CAD_EMP_ID, CAD_EMP_ID =CAD_EMP_ID IF NOT CAD_EMP_ID =''

I need to put something like this into a SET and TYPE statement, If this is not the correct format please explain.

Thank you

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

-SET &TODAY = &YYMD;
-SET &LASTDT2= AYMD (&YYMD, -2, 'I8YYMD');

-SET &DAYMIN2 = CHGDAT('YYMD', 'MDYY', &LASTDT2, 'A8');
-SET &TODAYDATE2 = CHGDAT('YYMD', 'MDYY', &TODAY, 'A8');

WHERE INCIDENT_SUPPLEMENTS.APPROVAL_DATE GE '&DAYMIN2.EVAL';
WHERE INCIDENT_SUPPLEMENTS.APPROVAL_DATE LT '&TODAYDATE2.EVAL';

Thank you for the feedback but the problem with the WHERE clause is that INCIDENT_SUPPLMENTS.APPROVAL is pulling from a ORACLE database and is joined with a SQL SERVER database. So when I put in a WHERE clause it doesnt print to the screen the SQL SERVER information that I want to show to the screen since it is tied into the WHERE clause to. Please review below code for what I mean. I would need to create a variable that would be shown to the screen if APPROVAL_DATE IS IN THE DATABASE and not shown to the screen if APPROVAL_DATE is not in the database.

 -* 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,OFFICERS.CAD_EMP_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES , OFFICERS
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 OFFICERS.OFFICER_ID=EMPLOYEES.OFFICER_ID;

-*and INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= trunc(sysdate) -2 and INCIDENT_SUPPLEMENTS.APPROVAL_DATE < trunc(sysdate);
-*and ROWNUM <= 10;


IF (INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= trunc(sysdate) -2 and INCIDENT_SUPPLEMENTS.APPROVAL_DATE < trunc(sysdate))
AND HAS A CAD_EMP_ID, CAD_EMP_ID =CAD_EMP_ID IF NOT CAD_EMP_ID =''

-*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
EMPLOYEES.OFFICER_ID
BY CAD_EMP_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 CAD_EMP_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTER AS J1

DEFINE FILE OFFNSDAT
APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD'); 
-*MINUSONEDATE/I8YYMD=&YYMD-1;
-*MINUSTWODATE/I8YYMD=&YYMD-2;
-*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

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

TABLE FILE  OFFNSDAT
HEADING CENTER
"&SUB_ASG"

PRINT UPLASTNAME AS ''

APPROVAL_DATE1 AS 'CLOSED OFFENSE'
-*MINUSONEDATE
-*MINUSTWODATE
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_DATE1 EQ MINUSONEDATE;
-*WHERE APPROVAL_DATE1 EQ FDATEYST;

-*WHERE APPROVAL_DATE1 LE &YYMD 

-*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,
$
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

 

I was hoping to get a SET statement similiar to this but with APPROVAL_DATE between a IF ELSE statement:

 SET &HDG_BEAT = IF &BEAT EQ _FOC_NULL THEN 'ALL' ELSE &BEAT; 

So if APPROVAL DATE DOES NOT FALL between today and 2 days ago it would = '', else APPROVAL_DATE = APPROVAL_DATE

Hi QuickLearner,

No offense, but you really need to do some training and learning of WF outside the Forum. Since you're posting, it's all about basic code that you're asking us to do for you :-)


What do you want to show ? The CAD_EMP_ID that have an approval as of last two days ?

If so

-SET &LASTDT2= AYMD (&YYMD, -2, 'I8YYMD');

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
    EMPLOYEES.OFFICER_ID
BY CAD_EMP_ID
WHERE INCIDENT_SUPPLEMENTS.APPROVAL_DATE GE &LASTDT2;
-*ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID
END
-RUN

Will do it. But be careful using SUM, it will display only one value of each SUM fields per CAD_EMP_ID. Meaning, per example, that if a CAD_EMP_ID may have more than one INC_REPORT_NUMBER, only one will be shown (last or first, can't remember)

But to answer your last post it will only be possible if your DB APPROVAL_DATE is also "saved" onto a variable.
If so, this is possible:

-SET &LASTDT2= AYMD (&YYMD, -2, 'I8YYMD');
-SET &APPROVED = IF &APPDATE GE &LASTDT2 THEN 'APPROVED' ELSE '';

You don't need to care about today date in the comparison since your APPROVAL_DATE cannot be greater than today...

quote:

MartinY

Thank you for all your help, it is well appreciated. It may be simple to you but it is a little complicated for me, But I have learned so much in the last 2 weeks. All the code you see I have written from searching these forms and asking what you consider to be "basic" code. I'm happy at how far I have come in 2 weeks with no BOOK. Much again much respect and thank you for your help!!

I added APPROVAL_DATE1 TO THE CODE PLEASE review if this is correct coding, it seems to give me the same results as before:

-SET &LASTDT2= AYMD (&YYMD, -2, 'I8YYMD');


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
EMPLOYEES.OFFICER_ID
BY CAD_EMP_ID
-*ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID
WHERE INCIDENT_SUPPLEMENTS.APPROVAL_DATE GE &LASTDT2;

END
-RUN

DEFINE FILE OFFNSDAT
APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD'); 
-*MINUSONEDATE/I8YYMD=&YYMD-1;
-*MINUSTWODATE/I8YYMD=&YYMD-2;
-*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

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


-*SET &DISDATA=APPROVAL_DATE

-*-SET &HDG_BEAT = IF &BEAT EQ _FOC_NULL THEN 'ALL' ELSE &BEAT;

-SET &LASTDT2= AYMD (&YYMD, -2, 'I8YYMD');
-SET &APPROVED = IF APPROVAL_DATE1 GE &LASTDT2 THEN APPROVAL_DATE1 ELSE '';


TABLE FILE  OFFNSDAT
HEADING CENTER
"&SUB_ASG"

PRINT UPLASTNAME AS ''

-*APPROVAL_DATE1 AS 'CLOSED OFFENSE'

&APPROVED AS 'CLOSED OFFENSE'
  

FULL 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,OFFICERS.CAD_EMP_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES , OFFICERS
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 OFFICERS.OFFICER_ID=EMPLOYEES.OFFICER_ID;

-*and INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= trunc(sysdate) -2 and INCIDENT_SUPPLEMENTS.APPROVAL_DATE < trunc(sysdate);
-*and ROWNUM <= 10;


-*IF (INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= trunc(sysdate) -2 and INCIDENT_SUPPLEMENTS.APPROVAL_DATE < trunc(sysdate))
-*AND HAS A CAD_EMP_ID, CAD_EMP_ID =CAD_EMP_ID IF NOT CAD_EMP_ID =''

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

TABLE 
ON TABLE HOLD AS OFFNSDAT
END

-*RUN


-*EXIT

-RUN

-*?FF HOLD
-*-EXIT

-SET &LASTDT2= AYMD (&YYMD, -2, 'I8YYMD');


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
EMPLOYEES.OFFICER_ID
BY CAD_EMP_ID
-*ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID
WHERE INCIDENT_SUPPLEMENTS.APPROVAL_DATE GE &LASTDT2;

END
-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 CAD_EMP_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTER AS J1

DEFINE FILE OFFNSDAT
APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD'); 
-*MINUSONEDATE/I8YYMD=&YYMD-1;
-*MINUSTWODATE/I8YYMD=&YYMD-2;
-*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

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


-*SET &DISDATA=APPROVAL_DATE

-*-SET &HDG_BEAT = IF &BEAT EQ _FOC_NULL THEN 'ALL' ELSE &BEAT;

-SET &LASTDT2= AYMD (&YYMD, -2, 'I8YYMD');
-SET &APPROVED = IF APPROVAL_DATE1 GE &LASTDT2 THEN APPROVAL_DATE1 ELSE '';


TABLE FILE  OFFNSDAT
HEADING CENTER
"&SUB_ASG"

PRINT UPLASTNAME AS ''

-*APPROVAL_DATE1 AS 'CLOSED OFFENSE'

&APPROVED AS 'CLOSED OFFENSE'


-*MINUSONEDATE
-*MINUSTWODATE
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_DATE1 EQ MINUSONEDATE;
-*WHERE APPROVAL_DATE1 EQ FDATEYST;

-*WHERE APPROVAL_DATE1 LE &YYMD 

-*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,
$
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:

I'm happy at how far I have come in 2 weeks with no BOOK

Imagine how happy you will be with a book! WebFOCUS 7.6.x Documentation

Don't know a single professional who hasn't opened "the book" as he/she laughs all the way to the bank..

Because of your lack of experience, training or documentation reading, you have fallen into a simple trap and mixed together 2 of the WebFOCUS languages. In this case you are using TABLE variables within Dialogue Manager expression. Cannot work. Move the expression into a DEFINE.

I figured this out. Thank you for all your help, below is the code:

DEFINE FILE OFFNSDAT
APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD'); 
CURRDATE/YYMD=&YYMD;
TWODAYSPRIOR/YYMD=CURRDATE-2;
DISPLAYDATE/YYMD=IF APPROVAL_DATE1 GE TWODAYSPRIOR THEN APPROVAL_DATE1 ELSE ''; END

Edit the first post of this thread and add [SOLVED] at the beginning of the post's title.