I first attempted to write this .fex using the GUI in 8.206,...

[Brad Gronli]

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.

[Martin Yergeau]

What is that Maybe your error is there

END

TABLE ON TABLE HOLD AS PENDING

END

[Brad Gronli]

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.

[Drew DeBaecke]

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

[David Beagan]

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

[Brad Gronli]

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.