I've been working on this situation for the past day and a half and I'm stumped. I've gone through the forum and research the mounds of technical manuals but I can't seem to get it to work. It's a simple if/then statement, but the problem is in getting my field value. I'm comparing a datetime stamp month to the current month. When they're equal then DT_GO = 'Y'. Currently, when run the process returns one record where the value for the DT_GO field is 'Y', however, I can't get the value into the if/then statement. I feel like I've gone through a hundred different gyrations of the code, but none are bringing me the value of the DT_GO field. Here's the code I'm working with:

-*SQL STATEMENT HERE*-
END
-RUN

DEFINE FILE SQLOUT
    CUR_MONTH/A2 = '&DATEM';
    CUR_YEAR/A4 = '&DATEYY';
    DT_MONTH/A2 = EDIT(HPART(LAST_ALTERED_TIMESTAMP, 'MONTH','I2'));
    DT_YEAR/A4 = EDIT(HPART(LAST_ALTERED_TIMESTAMP, 'YEAR','I4'));
    DT_GO/A2 = IF DT_MONTH EQ CUR_MONTH THEN 'Y' ELSE 'N';
END
-RUN

-DEFAULTH &DT_GOOD = ' '
READFILE SQLOUT
-SET &DT_GOOD = 'DT_GO.EVAL';
-IF &DT_GOOD EQ 'Y' THEN GOTO RUNREPORT;


-GETOUT
-TYPE 'THIS PROGRAM IS TERMINATED';
-EXIT;


-RUNREPORT

TABLE FILE SQLOUT
-*PRINT THE REST OF THE REPORT 

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

It's sure that we're missing some code to evaluate the problem, but one thing I can say is that you can't use DEFINEd fields outside of a TABLE FILE ... END.

DEFINEd field only exist in a TABLE FILE ... END if they are used in. If you want to use them after the TABLE FILE ... END, you must HOLD them in a file.

Example
If I do:

DEFINE FILE CAR
NCNTRY /A10 = IF COUNTRY EQ 'W GERMANY' THEN 'OTHER' ELSE COUNTRY;
END
TABLE FILE CAR
BY COUNTRY
END

Then NCNTRY will not be available.

If I want to use NCNTRY, I need at least to PRINT it to have it displayed on report and with a HOLD, I can then READ it:

DEFINE FILE CAR
NCNTRY /A10 = IF COUNTRY EQ 'W GERMANY' THEN 'OTHER' ELSE COUNTRY;
END
TABLE FILE CAR
PRINT NCNTRY
BY COUNTRY
ON TABLE HOLD AS TMP FORMAT BINARY
END
-RUN

-SET &NBCNTRY = &LINES;

-REPEAT LOOP &NBCNTRY TIMES
-READFILE TMP
-RUN
-TYPE NCNTRY = &NCNTRY
-LOOP

I'm not sure about this code.

1) You declare the output format of the HPART function as I2, but the DT_MONTH column is A2.

2) "-SET &DT_GOOD = 'DT_GO.EVAL';" means nothing - this isn't valid WebFOCUS code. At the very least it should be "-SET &DT_GOOD = &DT_GO;"

3) As far as I can tell, that DEFINE has no impact on the READFILE statement (which should be -READFILE). You have to TABLE FILE SQLOUT and create a HOLD file, referencing the appropriate DEFINE columns.

Hey Martin Y

Ok, that makes sense. I'll put the SQLOUT and the DEFINE fields in a HOLD file and then run my If/Then statement against the HOLD file. I'll go back and work with that understanding.

Thanks,
Pondog

Hey Francis,
Thanks for the code clarification. Like I mentioned, I've been through a hundred different gyrations of trying to get this to work so I'm not surprised I'm a little codeblind in some aspects. I appreciate your knowledge as I try to teach myself to code.

Pondog.

Something like this:

-*SQL STATEMENT HERE*-
END
-RUN

DEFINE FILE SQLOUT
    CUR_MONTH/A2 = '&DATEM';
    CUR_YEAR/A4 = '&DATEYY';
    DT_MONTH/A2 = EDIT(HPART(LAST_ALTERED_TIMESTAMP, 'MONTH','I2'));
    DT_YEAR/A4 = EDIT(HPART(LAST_ALTERED_TIMESTAMP, 'YEAR','I4'));
    DT_GO/A2 = IF DT_MONTH EQ CUR_MONTH THEN 'Y' ELSE 'N';
END
-RUN

TABLE FILE SQLOUT
PRINT DT_GO
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

-READFILE H001

-IF &DT_GO EQ 'Y' THEN GOTO RUNREPORT;

This process is going to be a pre-process fex that will check the datetime stamp on a file and then either run the report or not. Here's what I came up with that seems to work. The extra SQLOUT fields are for test purposes. I'll end up cleaning up the code and removing all the extraneous, non-functional, stuff.

 -SET &ECHO=ALL;


ENGINE DB2 SET DEFAULT_CONNECTION UABHS
SET EXCELSERVURL=''
-RUN

SQL DB2 PREPARE SQLOUT FOR

SELECT
    SYSTEM_TABLE_NAME,
    SYSTEM_TABLE_SCHEMA,
    LAST_ALTERED_TIMESTAMP

FROM
	QSYS2.SYSTABLES SYSTABLES
WHERE
	(SYSTEM_TABLE_NAME = 'SUNRISE' AND SYSTEM_TABLE_SCHEMA = 'MSOFILES'); -*test files where months DO match
-*	(SYSTEM_TABLE_NAME = 'ADRERR' AND SYSTEM_TABLE_SCHEMA = 'MSOFILES'); -*test files where months DON'T match
END
-RUN

DEFINE FILE SQLOUT
    CUR_MONTH/A2 = '&DATEM';
    CUR_YEAR/A4 = '&DATEYY';
    DT_MONTH/I2 = HPART(LAST_ALTERED_TIMESTAMP, 'MONTH','I2');
    DT_MONTH2/A2 = EDIT(DT_MONTH);
    DT_MONTH3/A2 = EDIT(HPART(LAST_ALTERED_TIMESTAMP, 'MONTH','I2'));
    DT_YEAR/A4 = EDIT(HPART(LAST_ALTERED_TIMESTAMP, 'YEAR','I4'));
    DT_GO/A2 = IF DT_MONTH3 EQ CUR_MONTH THEN 'Y' ELSE 'N';
END
-RUN


TABLE FILE SQLOUT
PRINT

	SYSTEM_TABLE_NAME
	SYSTEM_TABLE_SCHEMA
	LAST_ALTERED_TIMESTAMP
	DT_MONTH
	DT_MONTH2
	DT_MONTH3
	CUR_MONTH
	DT_YEAR
	CUR_YEAR
    DT_GO

ON TABLE HOLD AS SQLHOLD
END
-RUN

-READFILE SQLHOLD
-DEFAULTH &DT_GO = DT_GO.EVAL;
-RUN

-IF &DT_GO EQ 'Y' THEN GOTO RUNREPORT;



-GETOUT
-TYPE 'THIS PROGRAM IS TERMINATED';
-EXIT;


-RUNREPORT
-TYPE 'THIS PROGRAM IS IS ACTUALLY RUN';
-EXIT;
 

Do you need this statement?

-DEFAULTH &DT_GO = DT_GO.EVAL;

quote:

Do you need this statement?
-DEFAULTH &DT_GO = DT_GO.EVAL;

Especially when it's not a valid WF syntax...

Maybe I should have clarified, this fex will be used as a ReportCaster pre-process fex. I don't need a prompt, so I thought the -DEFAULTH statement was necessary. Thanks a bunch for helping me to get this portion of my report working. I appreciate it.

Pondog

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