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,

What version of WebFOCUS are you running?

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

I am using version 8008.

When I run the SQL query in a RDBMS it returns the expected rows.

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

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

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.

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.

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.