Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Error (FOC003) THE FIELDNAME IS NOT RECOGNIZED
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Error (FOC003) THE FIELDNAME IS NOT RECOGNIZED
 Login/Join
 
Member
posted
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=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

This message has been edited. Last edited by: Rageoftheage,
 
Posts: 5 | Registered: June 04, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
What version of WebFOCUS are you running?


WebFOCUS 8206, Unix, Windows
 
Posts: 1658 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2159 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
I am using version 8008.

When I run the SQL query in a RDBMS it returns the expected rows.
 
Posts: 5 | Registered: June 04, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1658 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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=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
-EXIT
 
Posts: 5 | Registered: June 04, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1643 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 5 | Registered: June 04, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1658 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Error (FOC003) THE FIELDNAME IS NOT RECOGNIZED

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.