Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Can't get value into if/then statement

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Can't get value into if/then statement
 Login/Join
 
Platinum Member
posted
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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,


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Can't get value into if/then statement

Copyright © 1996-2020 Information Builders