Brad Gronli Posted March 8, 2022 Share Posted March 8, 2022 I first attempted to write this .fex using the GUI in 8.206, but the results were not correct. So, I then turned to writing the SQL directly in the code, which mostly works. However, I am getting an error telling it cannot find the hold file used to store the results of the run, before they are printed to Excel. I should point out here that the output comes up as HTML, instead of Excel. Below is the code, followed by what I see when it is run. I have used this type of code in the past, when we were still in 7.7, but I am not seeing why the file is not being found or if there is some new part I am missing, such as having to add END after the line with the semi-colon. Any thoughts Thanks. -SET &ECHO = ALL; ENGINE INT CACHE SET ON SET PAGE-NUM=NOLEAD SET SQUEEZE=ON -DEFAULTH &WF_HTMLENCODE=ON; SET HTMLENCODE=&WF_HTMLENCODE SET HTMLCSS=ON -DEFAULTH &WF_EMPTYREPORT=ON; SET EMPTYREPORT=&WF_EMPTYREPORT -DEFAULTH &WF_SUMMARY='Summary'; -DEFAULTH &WF_TITLE='Pending Disbursements'; SQL SELECT A.ACCOUNT_CD, A.SWDN, A.SWDN_LINE, A.AGENCY_DOC_NBR, A.VOUCHER_DT, A.VOUCHER_STATUS, A.VOUCHER_REASON, A.APRV_AUDITOR_DT, A.TRN_AMT, B.USER_NBR FROM (SELECT ACCOUNT_CD, SWDN, SWDN_LINE, AGENCY_DOC_NBR, VOUCHER_DT VOUCHER_STATUS, VOUCHER_REASON, APRV_AUDITOR_DT, TRN_AMT FROM DISBPND WHERE OLO = '770000' AND TRN_AMT <> 0.00) A LEFT JOIN (SELECT OLO,1,2, GF_CD, SF_CD, FID, BE_CD, IBI, CAT, APPR_YR, AGENCY_DOC_NBR, TRN_AMT, USER_NBR FROM TRNHIST WHERE OLO = '770000') B ON A.ACCOUNT_CD = SUBSTR(B.OLO,1,2)||B.GF_CD||B.SF_CD||B.FID||B.BE_CD||B.IBI||B.CAT||B.APPR_YR AND SUBSTR(A.AGENCY_DOC_NBR,1,7) = SUBSTR(B.AGENCY_DOC_NBR,1,7) AND A.TRN_AMT = B.TRN_AMT; END TABLE ON TABLE HOLD AS PENDING END -RUN -* TABLE FILE PENDING PRINT ACCOUNT_CD SWDN SWDN_LINE AGENCY_DOC_NBR VOUCHER_DT VOUCHER_STATUS VOUCHER_REASON APRV_AUDITOR_DT TRN_AMT/D12.2C BY ACCOUNT_CD NOPRINT ON TABLE PCHOLD FORMAT XLSX ON TABLE NOTOTAL ON TABLE SET CACHELINES 100 ON TABLE SET GRWIDTH 1 ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$ TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, $ ENDSTYLE END -RUN View source from the output shows this. SQL SELECT A.ACCOUNT_CD, A.SWDN, A.SWDN_LINE, A.AGENCY_DOC_NBR, A.VOUCHER_DT, A.VOUCHER_STATUS, A.VOUCHER_REASON, A.APRV_AUDITOR_DT, A.TRN_AMT, B.USER_NBR FROM (SELECT ACCOUNT_CD, SWDN, SWDN_LINE, AGENCY_DOC_NBR, VOUCHER_DT, VOUCHER_STATUS, VOUCHER_REASON, APRV_AUDITOR_DT, TRN_AMT FROM DISBPND WHERE OLO = 770000 AND TRN_AMT <> 0.00) A LEFT JOIN (SELECT OLO,1,2, GF_CD, SF_CD, FID, BE_CD, IBI, CAT, APPR_YR, AGENCY_DOC_NBR, TRN_AMT, USER_NBR FROM TRNHIST WHERE OLO = 770000) B ON A.ACCOUNT_CD = SUBSTR(B.OLO,1,2)||B.GF_CD||B.SF_CD||B.FID||B.BE_CD||B.IBI||B.CAT||B.APPR_YR AND SUBSTR(A.AGENCY_DOC_NBR,1,7) = SUBSTR(B.AGENCY_DOC_NBR,1,7) AND A.TRN_AMT = B.TRN_AMT; END TABLE ON TABLE HOLD AS PENDING END -RUN 0 NUMBER OF RECORDS IN TABLE= 41 LINES= 41 0 HOLDING HTML FILE ON PC DISK -* TABLE FILE PENDING PRINT ACCOUNT_CD SWDN SWDN_LINE AGENCY_DOC_NBR VOUCHER_DT VOUCHER_STATUS VOUCHER_REASON APRV_AUDITOR_DT TRN_AMT/D12.2C BY ACCOUNT_CD NOPRINT ON TABLE PCHOLD FORMAT XLSX . . . 0 ERROR AT OR NEAR LINE 38 IN PROCEDURE pending_doc_dfs (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: PENDING (FOC009) Request failed validation, not executed. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted March 8, 2022 Share Posted March 8, 2022 What is that Maybe your error is there END TABLE ON TABLE HOLD AS PENDING END Link to comment Share on other sites More sharing options...
Brad Gronli Posted March 8, 2022 Author Share Posted March 8, 2022 Without that, it gets a syntax error when I try to save the file. I have that same type of coding in the older 7.7 code, which works without an issue. Link to comment Share on other sites More sharing options...
Drew DeBaecke Posted March 8, 2022 Share Posted March 8, 2022 I Think the extra line break makes a difference There are a few spots in FOCUS that require the line break, and this might be a code-tightening that finally caught up SQL SELECT TOP 20 * FROM CAR; TABLE ON TABLE HOLD AS HITHERE END Link to comment Share on other sites More sharing options...
David Beagan Posted March 9, 2022 Share Posted March 9, 2022 When I run this code: SQL SELECT * FROM CAR; END TABLE ON TABLE HOLD AS PENDING END TABLE FILE PENDING PRINT * ON TABLE PCHOLD FORMAT XLSX ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$ ENDSTYLE END In view source, I get this: 0 HOLDING HTML FILE ON PC DISK ... 1 0 NUMBER OF RECORDS IN TABLE= 42 LINES= 30 0 ERROR AT OR NEAR LINE 5 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC001) THE NAME OF THE FILE OR THE WORD 'FILE' IS MISSING 0 ERROR AT OR NEAR LINE 7 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: PENDING (FOC009) Request failed validation, not executed. Which seems to reproduce your issue. I change the code to the following and it produces the Excel output: SQL SELECT * FROM CAR; TABLE ON TABLE HOLD AS PENDING END TABLE FILE PENDING PRINT * ON TABLE PCHOLD FORMAT XLSX ON TABLE SET STYLE * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$ ENDSTYLE END Link to comment Share on other sites More sharing options...
Brad Gronli Posted March 9, 2022 Author Share Posted March 9, 2022 Originally, when I attempted to save the report, I did not have END after the semi-colon and would get a syntax error. AND A.TRN_AMT = B.TRN_AMT; TABLE ON TABLE HOLD AS PENDING END -RUN I then changed the code to this. AND A.TRN_AMT = B.TRN_AMT; END TABLE ON TABLE HOLD AS PENDING END -RUN After reading the above posts, I changed it to AND A.TRN_AMT = B.TRN_AMT; TABLE ON TABLE HOLD AS PENDING END and now I get the output in Excel. Thanks all. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now