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.
What would be the syntax in order to display it as:
FileName: REPORT_OF_20130905.pdf
The date field in the table contains the same date for all the rows as the date is to indicate the last time the table had a major change to its data. This date won't be displayed in the detail of the report, only the header which hopefully in the future I can get to actually save to the filename rather than show in the header.This message has been edited. Last edited by: sxschech,
You can't reference a field in a set statement. It doesn't know what is in the field. You have to create a define and just move the set statements there.
Prod/Dev: WebFOCUS 8.0.06 on Windows Server 2008/Tomcat , WebFOCUS DevStudio 8.0.06 on Windows 7
I figured out that I could change the format of the date in the hold file, but when I tried to follow your suggestion to concatenate this into the filename variable using Define I must have missed something.
-INCLUDE ATRGRADSQL
DEFINE ATRGRAD
FN/A60 = 'REPORT_OF_'||REFRESH_DATE||'.pdf';
END
TABLE FILE ATRGRAD
PRINT REFRESH_DATE
HEADING
"FileName: <FN "
END
I get the following error message.
quote:
ERROR AT OR NEAR LINE 3 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC001) THE NAME OF THE FILE OR THE WORD 'FILE' IS MISSING (FOC1517) UNRECOGNIZED COMMAND FN/A60 = 'REPORT_OF_'||REFRESH_DATE||'.PDF'; 0 ERROR AT OR NEAR LINE 10 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC406) THE FIELDNAME IS NOT RECOGNIZED: FN BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT
If I use
"FileName: FN " rather than "FileName: I don't get the error message, but it of course only displays the literal.
Ok, Thanks for pointing that out. I was trying to recreate a simplified version of the original code, so missed that key word.
I fixed that line, but now the error says:
quote:
ERROR AT OR NEAR LINE 5 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT 0 ERROR AT OR NEAR LINE 10 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC406) THE FIELDNAME IS NOT RECOGNIZED: FN BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT
ERROR AT OR NEAR LINE 4 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT 0 ERROR AT OR NEAR LINE 5 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: REFDATE 0 ERROR AT OR NEAR LINE 10 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC406) THE FIELDNAME IS NOT RECOGNIZED: FN BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT
I got some assistance from tech support and based on the example they provided, was able to get it to work. I had a little trouble at first, getting error messages when I tried against my data, and then noticed that the code IBI provided did not put a semicolon at the end of the -SET. Once I put in the semicolons, the code ran for my data without error.
Here is IBI's code
TABLE FILE CAR
PRINT DEALER_COST
WHERE RECORDLIMIT EQ 1
ON TABLE SAVE AS TEST1
END
-RUN
-READ TEST1 &TEST1.A7.
-SET &FileName = 'All' || &TEST1
TABLE FILE CAR
PRINT DEALER_COST
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
HEADING
"FileName: &FileName"
ON TABLE PCHOLD FORMAT HTML
END
One of the earlier error messages showed that REFRESH_DATE was 17 characters, so that is where I got the .A17. from.
And here is what I did (added some if statements to better match the actual code) for getting the date to show within the file name format.
-INCLUDE ATRGRADSQL
TABLE FILE ATRGRAD
PRINT REFRESH_DATE/HYYMDN AS 'REFDATE'
WHERE RECORDLIMIT EQ 1
ON TABLE SAVE AS REFDATES
END
-RUN
-READ REFDATES &REFDATES.A17.
-SET &RefDate = EDIT(&REFDATES, '99999999$$$$$$$$$');
-SET &GroupName = 'SF';
-SET &FileName = IF &GroupName = 'USF' THEN 'All' || &RefDate || &GroupName || '.pdf' ELSE 'All' || &RefDate || '.pdf';
TABLE FILE ATRGRAD
PRINT FIELD1
FIELD2
HEADING
"FileName: &FileName"
ON TABLE PCHOLD FORMAT HTML
END
This seems way too complicated - obviously I'm missing something....
DEFINE FILE XYZ
REFDATE/YYMD=20130603;
END
TABLE FILE XYZ
PRINT SOMEFIELD NOPRINT
WHERE SOMEDATE EQ REFDATE
HEADING
"Filename: <SOMEDATE .pdf "
ON TABLE PCHOLD FORMAT PDF
END
I tried your example, couldn't get it to work (based on my trying to understand your code). Also, the RefDate is coming from the table itself and your example is showing as hard coded, which you then are making equal to the date in the table. Not sure why I would need to make the date equal to the date in the table, seems unnecessary. Also the filename string is a lot more complicated than simply concatenating the date with .pdf, I have several IF statements and ampervariables from an html composer file that come together to derive the filename.
I only used a hard date and the where statement because I tested that against my own database which has thousands of dates.
I presume yours does too and you are using some other criterion to select your records such that they all have the same REFDATE.
Here I do it again using an order number (ONUM) as my criterion since each order can have only one order date (ODATE).
This gives: Filename: 2007/09/27 .pdf in the heading with a space between the date and .pdf.
TABLE FILE XYZ
PRINT ODATE NOPRINT
WHERE ONUM EQ '207361'
HEADING
"Filename: <ODATE .pdf "
ON TABLE PCHOLD FORMAT PDF
END
You would need to do a define to convert the date to your preferred format and add a spot marker to take out the space between the date and .pdf
The following gives: Filename: September 27, 2007.pdf
Of course you then have to take steps to make the filename match what you have in the header. Personally I wouldn't use the above date format for this because I'm against commas in filenames as a matter of principle. I would go for 2007-09-27.pdf
DEFINE FILE XYZ
NEWDATE/MtrDYY=ODATE;
END
TABLE FILE XYZ
PRINT NEWDATE NOPRINT
WHERE ONUM EQ '207361'
HEADING
"Filename: <NEWDATE<+0>.pdf "
ON TABLE PCHOLD FORMAT PDF
END
Thanks for continuing to look at this. The date that is being used in the table is the same for all records. It indicates the date that the table was refreshed/updated. I use this date so when the report is run, we know when the data were last refreshed/updated. There shouldn't be a need to have a where clause to determine the date in this particular set up. The person running the report shouldn't need to know the date in order to run the report, but would like it displayed on the report for the above reason. If I could think of another way to store the date for use on the report without taking up space in the table I could consider that. I'm pulling the date out in the format 20130918 Year Month Day. No spaces, dashes slashes, etc. It then needs to be concatenated with the rest of the name making up the filename which is derived through if statements evaluating the ampervariables passed to it from the html composer page. When the help desk IBI consultant tried to do something similar to what you showed with spot markers, he couldn't quite get it to work so that is why he suggested using the method with the -READ.
Not sure how I could sandwich together the filename with the date using your method since date is not the last item before .pdf
I think the way to make it work is to do most of what you have inside of a DEFINE instead of with SET statements. That way the REFRESH DATE is available within the report.
So something like:
DEFINE FILE XYZ
REFRESH_DATE_ALPHA/A8YYMD = REFRESH_DATE;
FILENAME/A60 = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');
FILENAME = IF &TTYGROUP = 'FOC_NONE' THEN &GRADRETEN || '_' || &STUPOP2 || REFRESH_DATE_ALPHA || '-' || FILENAME|| &FOREWORDNAME ELSE &GRADRETEN || '_' || &TTYGROUP_TEXT || REFRESH_DATE_ALPHA || '-' || FILENAME || &FOREWORDNAME;
FILENAME = IF &FILTERS EQ 'Yes' THEN FILENAME || '_StartEnd' || &STARTITEM || '.pdf' ELSE IF &FILTERS EQ 'Any' THEN FILENAME || '_StartAnyGrad' || &ENDITEM || '.pdf' ELSE FILENAME || '.pdf';
[other statements]
END
Don't know what I'm doing wrong, haven't been able to get your code to work. I get error message:
quote:
0 ERROR AT OR NEAR LINE 17 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: REFRESH_DATE_ALPHA 0 ERROR AT OR NEAR LINE 18 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: REFRESH_DATE_ALPHA 0 ERROR AT OR NEAR LINE 26 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC406) THE FIELDNAME IS NOT RECOGNIZED: FileName
Here is a very simplified version of my code based on what I thought was shown in your example:
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
ENGINE SQLORA SET DEFAULT_CONNECTION ODSP
SQL SQLORA PREPARE SQLOUT FOR
select '12345' as ID, '201340' as cohortterm, 'AA' as IncomingCollege, TO_DATE('20130915', 'yyyymmdd') as refresh_date from DUAL UNION
select '23456' as ID, '201340' as cohortterm, 'AA' as IncomingCollege, TO_DATE('20130915', 'yyyymmdd') as refresh_date from DUAL
END
TABLE FILE SQLOUT
PRINT
ID/A5 AS 'CWID'
COHORTTERM/A6 AS 'COHORTTERM'
INCOMINGCOLLEGE/A2 AS 'INCOMINGCOLLEGE'
REFRESH_DATE/HYYMDN
ON TABLE HOLD AS ATR
END
DEFINE FILE ATR
REFRESH_DATE_ALPHA/A8YYMD = REFRESH_DATE;
FileName/A60=IF INCOMINGCOLLEGE = 'NS' THEN COHORTTERM || '_' || REFRESH_DATE_ALPHA || '.pdf' ELSE INCOMINGCOLLEGE || '_' || REFRESH_DATE_ALPHA || '_' || 'XYZ.pdf';
END
TABLE FILE ATR
PRINT
CWID
COHORTTERM
INCOMINGCOLLEGE
HEADING
"Filename: <FileName"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
ENGINE SQLORA SET DEFAULT_CONNECTION PROD
SQL SQLORA PREPARE SQLOUT FOR
select '12345' as ID, '201340' as cohortterm, 'AA' as IncomingCollege, TO_DATE('20130915', 'YYYY/MM/DD') as refresh_date from DUAL UNION
select '23456' as ID, '201340' as cohortterm, 'AA' as IncomingCollege, TO_DATE('20130915', 'YYYY/MM/DD') as refresh_date from DUAL
;
END
DEFINE FILE SQLOUT
X_DATE/YYMD = HDATE(REFRESH_DATE, 'YYMD');
Y_DATE/I8YYMD = X_DATE;
Z_DATE/A8YYMD = EDIT(Y_DATE);
END
TABLE FILE SQLOUT
PRINT
ID/A5 AS 'CWID'
COHORTTERM/A6 AS 'COHORTTERM'
INCOMINGCOLLEGE/A2 AS 'INCOMINGCOLLEGE'
REFRESH_DATE
X_DATE
Y_DATE
Z_DATE
ON TABLE HOLD AS ATR
END
DEFINE FILE ATR
-*REFRESH_DATE_ALPHA/A8YYMD = REFRESH_DATE;
FileName/A60 = IF INCOMINGCOLLEGE EQ 'NS' THEN COHORTTERM || '_' || Z_DATE || '.pdf' ELSE INCOMINGCOLLEGE || '_' || Z_DATE || '_' || 'XYZ.pdf';
END
TABLE FILE ATR
PRINT
CWID
COHORTTERM
INCOMINGCOLLEGE
HEADING
"Filename: <FileName"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-EXIT
Note that I said “something like” the solution I suggested. I didn't want to imply that it was the exact code.
According to 1001 ways to work with dates in WebFocus:
“The process of converting a smartdate field to a legacydate field simply requires an equality statement..... For legacydate conversions the output length specification must match the date output. Four-digit years would require A8MDYY … The order of the month, day and year components of the output field should usually match the input field” - Page 53 in the Second edition of the book.
This was he basis of my suggestion.
Tom is suggesting that REFRESH_DATE is a DateTime value. I assumed it was purely a date – and didn't actually try out the A8YYMD.
I understand HDATE will produce a numeric value – which would then need to be converted to alpha as he demonstrates.
When I try your code as is, it works, when I try it with my actual fex it doesn't yet work. I must be missing something in the syntax when I converted the FileName from the -SET to the DEFINE section.
quote:
ERROR AT OR NEAR LINE 18 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC260) AN OPERATION IS MISSING AN ARGUMENT 0 ERROR AT OR NEAR LINE 16 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC266) IF .. THEN .. ELSE .. SYNTAX ERROR 0 ERROR AT OR NEAR LINE 29 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC406) THE FIELDNAME IS NOT RECOGNIZED: FileName BYPASSING TO END OF COMMAND
This is the code from the -SET
-SET &FileName = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');
-SET &FileName = IF &TTYGROUP = 'FOC_NONE' THEN &GRADRETEN || '_' || &STUPOP2 || &REFDATE || '-' || &FileName || &FOREWORDNAME ELSE &GRADRETEN || '_' || &TTYGROUP_TEXT || &REFDATE || '-' || &FileName || &FOREWORDNAME;
-SET &FileName = IF &FILTERS EQ 'Yes' THEN &FileName || '_StartEnd' || &STARTITEM || '.pdf' ELSE
- IF &FILTERS EQ 'Any' THEN &FileName || '_StartAnyGrad' || &ENDITEM || '.pdf' ELSE
- &FileName || '.pdf';
This is how it looks in the DEFINE. What I did was remove the amper from FileName, but I imagine there must be something more to it.
DEFINE FILE ATRGRAD
FileName = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');
FileName = IF &TTYGROUP = 'FOC_NONE' THEN &GRADRETEN || '_' || &STUPOP2 || Z_DATE || '-' || FileName || &FOREWORDNAME ELSE &GRADRETEN || '_' || &TTYGROUP_TEXT || Z_DATE || '-' || FileName || &FOREWORDNAME;
FileName = IF &FILTERS EQ 'Yes' THEN FileName || '_StartEnd' || &STARTITEM || '.pdf' ELSE
IF &FILTERS EQ 'Any' THEN FileName || '_StartAnyGrad' || &ENDITEM || '.pdf' ELSE
FileName || '.pdf';
END
Why is this marked as Solved then? In your DEFINE, you have no formats, that means it's a bullion result, 0 or 1. By doing ? FOCERROR number, you'll get an explanation of the error:
? 260
gives:
(FOC260) AN OPERATION IS MISSING AN ARGUMENT
An arithmetic or logical operation is missing an argument, possibly
two operations have been written in succession without a semi-colon
(;) separating them.
? 266
gives
(FOC266) IF .. THEN .. ELSE .. SYNTAX ERROR
Missing or extra IF, THEN, or ELSE clauses, or possibly a missing
semi-colon (;).
? 406
gives
(FOC406) THE FIELDNAME IS NOT RECOGNIZED:
The name of the data field is not on the list of fields for this
file. Check the spelling or the list of fieldnames and aliases
for the file.
This is only partial code, I would ensure there is a TOKEN 8, and, it's 60 bytes, which I kinda doubt... Debug yourself, 1 piece at a time...
-SET &FileName = IF &BYON EQ 'COHORTTERM' THEN 'All' ELSE GETTOK(&BYON,&BYON.LENGTH,8,'~',60,'A60');
-TYPE &FileName
-EXIT
The thread was marked as solved because IBI developed a solution that worked. However, after I marked it as solved, George Patton was suggesting an alternate approach and I thought since he offered that option, I should try it out. Unfortunately, I have spent way too much time on it, and still that approach doesn't seem to work for me and since I have a workable solution, I'm going to go with that rather than try to debug this.
Francis, I tried putting your suggestion of FileName/A200, now there is no error message, but the output is blank.
I am glad that IBI development gave you a solution - I agree that you should rather stick with it.
The forum is here to "help" - but to be helped the requestor needs to heed the advice.
I was admittedly getting a little frustrated reading this post, because everyone has been trying their utmost to help, and also because a week ago I had suggested:
quote:
You will need to use one of the date/time functions to first convert the date/time format to an alpha format.
And Tom pretty much said the same thing two days ago:
quote:
Read up on dates...
Like George said - sometimes we will share "something" as an idea, not necessarily a complete solution.
These "something"s can be used for further learning/reading - which very often helps other people that are just starting out with WebFOCUS.
So, use the example that works, but to develop your knowledge in WebFOCUS, perhaps the only task you should set yourself is simply to figure out how to convert your date/time field to an alpha format. Admittedly this will probably mean the you'll have to read a manual ;-)
In other words, get the code below to create a field called ALPHA_REFDATE to display your date in alpha format. If you can get that to work - post the code - and then I am sure we can help you!
DEFINE FILE ATRGRAD ALPHA_REFDATE/A???? = ?????REFRESH_DATE????? ; END
TABLE FILE ATRGRAD PRINT REFRESH_DATE ALPHA_REFDATE WHERE RECORDLIMIT EQ 1 END
WebFOCUS 8.2.06 mostly Windows Server
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008
You have FOC_NONE on the second line of those defines, which means that entire line will be ignored by FOCUS. The same happens to lines where any of those &var's contain FOC_NONE.
That's probably not what you intended.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
Twanette and others are precisely correct. You need to simplify and demonstrate that you are retrieving the correct value first before trying to concatenate it into a complex string.
We are all just trying to point out in the end that your REFRESH_DATE exists in the datbase you are pulling from and it therefore is eminently possible - and to the point BETTER coding style - to use a couple of DEFINES to get it into the format required.
Ultimately this is much simpler than doing a separate TABLE FILE to put the value into a separate file and then READing that file value into an Amper Variable.
PS - I did, if you look back to my DEFINE, show FILENAME/A60 = .....
You should try out Twanette's most recent suggestion - but it's a suggestion! - you will have to substitute some things for all those question marks !
Again, I would like to thank and let everyone know I appreciate their help and patience in working through this. Based on the fact that Tom's suggestion worked, the issue was at that point not with the date field, but something else. It seems the simpler one tries to make something for the user, the more complicated it gets behind the scenes. When I have time, I'm going to look into something Wep5622 pointed out in that post regarding the FOC_NONE as that might be why the output is blank.
I didn't see it mentioned but FPRINT can also be used to easily convert numerics to alphas including Date-Time and Legacy dates. Do a search on FPRINT to find out more.
In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle