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,
WebFOCUS 8.1.05 Windows, All Outputs
May 31, 2016, 10:33 AM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
May 31, 2016, 10:40 AM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 31, 2016, 10:49 AM
Pondog
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
WebFOCUS 8.1.05 Windows, All Outputs
May 31, 2016, 10:52 AM
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.
WebFOCUS 8.1.05 Windows, All Outputs
May 31, 2016, 11:12 AM
Francis Mariani
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;
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 31, 2016, 11:38 AM
Pondog
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;
WebFOCUS 8.1.05 Windows, All Outputs
May 31, 2016, 11:40 AM
Francis Mariani
Do you need this statement?
-DEFAULTH &DT_GO = DT_GO.EVAL;
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 31, 2016, 11:46 AM
MartinY
quote:
Do you need this statement? -DEFAULTH &DT_GO = DT_GO.EVAL;
Especially when it's not a valid WF syntax...
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
May 31, 2016, 11:52 AM
Pondog
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.
PondogThis message has been edited. Last edited by: Pondog,