[SOLVED]Error (FOC003) THE FIELDNAME IS NOT RECOGNIZED
Hi all, I am getting the following error:
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 20190701 20180701 20190731 20180731 2019 M 2019 July 0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3 0 ERROR AT OR NEAR LINE 117 IN PROCEDURE FICR016AFOCEXEC * (FOC003) THE FIELDNAME IS NOT RECOGNIZED: SUM(LYLTY_CHRG_USD_AMT) BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT
The field it does not recognize on 117 is used early in a SQL statement and no error was caused. When I comment out all the fields it 'does not recognize' I get the following:
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 20190701 20180701 20190731 20180731 2019 M 2019 July 0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3 0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3
My code is as follows:
ENGINE SQLORA SET DEFAULT_CONNECTION ******* SQL SQLORA PREPARE SQLOUT FOR SELECT PRPTY_CD, SUM(LYLTY_CHRG_USD_AMT), SUM(DISTB_CHRG_USD_AMT), SUM(DRVD_USD_RM_REV_AMT), SUM(REIMB_USD_AMT) FROM CERTIFIED.WE_HTL_RPT_DTL E -*WHERE ACCT_PRD_DT >= (TO_DATE(TO_CHAR(TO_DATE('&PRD_HEAD','MONTH-YYYY'), 'MM/DD/YYYY'), 'MM/DD/YYYY')) -*AND ACCT_PRD_DT < [TO_DATE[TO_CHAR[ADD_MONTHS[TO_DATE['&PRD_HEAD','MONTH-YYYY'), 1), 'MM/DD/YYYY'), 'MM/DD/YYYY')) GROUP BY PRPTY_CD ORDER BY PRPTY_CD;
-RUN -*'PLYH' TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS FICR016A_HOLDF1 FORMAT ALPHA END
TABLE FILE FICR016A_HOLDF1 PRINT PRPTY_CD AS 'Property Code' -* SUM(LYLTY_CHRG_USD_AMT) AS 'Loyalty Inv Charge' -* SUM(DISTB_CHRG_USD_AMT) AS 'Distribution Charge' -* SUM(DRVD_USD_RM_REV_AMT) AS 'Room Revenue' -* SUM(REIMB_USD_AMT) AS 'Redemption Reimbursement'
HEADING "&ReportName - Invoice Totals" "Activity Month: &PRD_HEAD" "Report Run Date: &ReportDateTimeStamp" "&ReportLocation" "Property: &PRPTY_CD" "All currency is reported in USD unless otherwise indicated" "&SecurityLevelDSC" ""
ON TABLE SET PAGE-NUM OFF ON TABLE PCHOLD AS EX2 FORMAT EXL2K OPEN ON TABLE SET HTMLCSS ON ON TABLE SET STYLE *
TYPE=TITLE, STYLE=BOLD, BACKCOLOR=RGB(198 219 255), JUSTIFY=CENTER, $ ENDSTYLE ENDThis message has been edited. Last edited by: Rageoftheage,
September 04, 2019, 04:00 PM
BabakNYC
What version of WebFOCUS are you running?
WebFOCUS 8206, Unix, Windows
September 04, 2019, 04:01 PM
MartinY
Take a look at the data itself (from outside WF using SQL query or something else than WF)
Your data may be corrupted or contain invalid data for that column
May contains values that cannot by SUMmed
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
September 04, 2019, 04:41 PM
Rageoftheage
I am using version 8008.
When I run the SQL query in a RDBMS it returns the expected rows.
September 04, 2019, 05:08 PM
BabakNYC
I'm not sure if I'm seeing this right. What is the E to the right of
FROM CERTIFIED.WE_HTL_RPT_DTL E
I'm also not sure why you have to hold the answer set in an ALPHA file before you go to the final report. Additionally, I don't know if you need the word OPEN on the line FORMAT EXL2K. Can you try this version of your report:
ENGINE SQLORA SET DEFAULT_CONNECTION *******
SQL SQLORA PREPARE SQLOUT FOR
SELECT PRPTY_CD,
SUM(LYLTY_CHRG_USD_AMT),
SUM(DISTB_CHRG_USD_AMT),
SUM(DRVD_USD_RM_REV_AMT),
SUM(REIMB_USD_AMT)
FROM CERTIFIED.WE_HTL_RPT_DTL E
-*WHERE ACCT_PRD_DT >= (TO_DATE(TO_CHAR(TO_DATE('&PRD_HEAD','MONTH-YYYY'), 'MM/DD/YYYY'), 'MM/DD/YYYY'))
-*AND ACCT_PRD_DT < [TO_DATE[TO_CHAR[ADD_MONTHS[TO_DATE['&PRD_HEAD','MONTH-YYYY'), 1), 'MM/DD/YYYY'), 'MM/DD/YYYY'))
GROUP BY PRPTY_CD
ORDER BY PRPTY_CD;
TABLE FILE SQLOUT
PRINT
PRPTY_CD AS 'Property Code'
HEADING
"&ReportName - Invoice Totals"
"Activity Month: &PRD_HEAD"
"Report Run Date: &ReportDateTimeStamp"
"&ReportLocation"
"Property: &PRPTY_CD"
"All currency is reported in USD unless otherwise indicated"
"&SecurityLevelDSC"
""
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=8, TITLETEXT=Invoice Totals, $
TYPE=HEADING,WRAP=OFF,LINE=1,JUSTIFY=LEFT, COLSPAN=8, $
TYPE=HEADING,WRAP=OFF,LINE=2,JUSTIFY=LEFT, COLSPAN=8, $
TYPE=HEADING,WRAP=OFF,LINE=3,JUSTIFY=LEFT, COLSPAN=8, $
TYPE=HEADING,WRAP=OFF,LINE=4,JUSTIFY=LEFT, COLSPAN=8, $
TYPE=HEADING,WRAP=OFF,LINE=5,JUSTIFY=LEFT, COLSPAN=8, $
TYPE=HEADING,WRAP=OFF,LINE=6,JUSTIFY=LEFT, COLSPAN=8, $
TYPE=HEADING,WRAP=OFF,LINE=7,JUSTIFY=LEFT, COLSPAN=8, $
TYPE=HEADING,WRAP=ON,LINE=8,JUSTIFY=LEFT, COLSPAN=8, $
TYPE= DATA, BORDER-RIGHT=0.1, BORDER-LEFT=0.1, $
TYPE=TITLE, STYLE=BOLD, BACKCOLOR=RGB(198 219 255), JUSTIFY=CENTER, $
ENDSTYLE
END
-RUN
WebFOCUS 8206, Unix, Windows
September 04, 2019, 06:02 PM
Rageoftheage
I had it holding the answer just to try it because it was not working without holding. The E and OPEN were just sloppy mistakes from all the editing im doing as this is my first WF Report.
The version you asked me to try did not produce any output. However I removed the E and OPEN and I was able to get output as long as I keep the summed variables commented out, so thank you for pointing that out! I need those fields printed and I am confused as to why I am getting the FOC003 error in the print unit but not the SQL unit.
This is the iteration of code that produced an output for me:
ENGINE SQLORA SET DEFAULT_CONNECTION ********** SQL SQLORA PREPARE SQLOUT FOR SELECT PRPTY_CD, SUM(LYLTY_CHRG_USD_AMT), SUM(DISTB_CHRG_USD_AMT), SUM(DRVD_USD_RM_REV_AMT), SUM(REIMB_USD_AMT) FROM CERTIFIED.WE_HTL_RPT_DTL WHERE ACCT_PRD_DT >= (TO_DATE(TO_CHAR(TO_DATE('&PRD_HEAD','MONTH-YYYY'), 'MM/DD/YYYY'), 'MM/DD/YYYY')) AND ACCT_PRD_DT < [TO_DATE[TO_CHAR[ADD_MONTHS[TO_DATE['&PRD_HEAD','MONTH-YYYY'), 1), 'MM/DD/YYYY'), 'MM/DD/YYYY')) GROUP BY PRPTY_CD ORDER BY PRPTY_CD;
-RUN
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS FICR016A_HOLDF1 FORMAT ALPHA END
TABLE FILE FICR016A_HOLDF1 PRINT PRPTY_CD AS 'Property Code' -* SUM(LYLTY_CHRG_USD_AMT) AS 'Loyalty Inv Charge' -* SUM(DISTB_CHRG_USD_AMT) AS 'Distribution Charge' -* SUM(DRVD_USD_RM_REV_AMT) AS 'Room Revenue' -* SUM(REIMB_USD_AMT) AS 'Redemption Reimbursement'
HEADING "&ReportName - Invoice Totals" "Activity Month: &PRD_HEAD" "Report Run Date: &ReportDateTimeStamp" "&ReportLocation" "Property: &PRPTY_CD" "All currency is reported in USD unless otherwise indicated" "&SecurityLevelDSC" ""
ON TABLE SET PAGE-NUM OFF ON TABLE PCHOLD AS EX2 FORMAT EXL2K ON TABLE SET HTMLCSS ON ON TABLE SET STYLE *
TYPE=TITLE, STYLE=BOLD, BACKCOLOR=RGB(198 219 255), JUSTIFY=CENTER, $ ENDSTYLE END -EXIT
September 05, 2019, 04:06 AM
Wep5622
ENGINE SQLORA SET DEFAULT_CONNECTION **********
SQL SQLORA PREPARE SQLOUT FOR
SELECT PRPTY_CD,
SUM(LYLTY_CHRG_USD_AMT),
SUM(DISTB_CHRG_USD_AMT),
SUM(DRVD_USD_RM_REV_AMT),
SUM(REIMB_USD_AMT)
FROM CERTIFIED.WE_HTL_RPT_DTL
WHERE ACCT_PRD_DT >= (TO_DATE(TO_CHAR(TO_DATE('&PRD_HEAD','MONTH-YYYY'), 'MM/DD/YYYY'), 'MM/DD/YYYY'))
AND ACCT_PRD_DT < [TO_DATE[TO_CHAR[ADD_MONTHS[TO_DATE['&PRD_HEAD','MONTH-YYYY'), 1), 'MM/DD/YYYY'), 'MM/DD/YYYY'))
GROUP BY PRPTY_CD
ORDER BY PRPTY_CD;
-* You did not terminate your prepared statement
END
-RUN
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS FICR016A_HOLDF1 FORMAT ALPHA
END
-* Check the output of this for your column names
?FF FICR016A_HOLDF1
If you do indeed get column-names like 'SUM(LYLTY_CHRG_USD_AMT)', you should probably reference them quoted in your TABLE FILE requests.
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 :
September 05, 2019, 11:03 AM
Rageoftheage
The report is working after writing it like this:
TABLE FILE SQLOUT PRINT PRPTY_CD AS 'Property Code' SUM_LYLTY_CHRG_USD_AMT AS 'Loyalty Inv Charge' SUM_DISTB_CHRG_USD_AMT AS 'Distribution Charge' SUM_DRVD_USD_RM_REV_AMT AS 'Room Revenue' SUM_REIMB_USD_AMT AS 'Redemption Reimbursement'
Thanks everyone.
September 05, 2019, 11:20 AM
BabakNYC
I guess in our zeal to help you, we didn't notice that you are trying to print a field that doesn't exist. SUM(FIELDNAME) in the SQLOUT won't be used as a column name. The best way to control the fieldnames in your hold files is to use SET HOLDLIST, SET ASNAME and SET TITLE commands. Look these SET commands up in the manual here https://infocenter.information...rce%2Fsetcore116.htm so you find out how they can help you with the hold files. However, it should be unnecessary to HOLD the output of SQLOUT to get the report you're looking for.