As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.
I want to display null values as 0 in my report. Using the NVL command in my fex broke the report even though I am calling Oracle SQL. The SET NODATA command didnt appear to do anything at all. I have tried both SET NODATA=0 and SET NODATA='0'
Using NVL in a RDBMS returned what was expected so I know the values are null and not a space.
Can anyone tell me what I am doing wrong? Thank you.
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; END -RUN
SET NODATA=0 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'This message has been edited. Last edited by: FP Mod Chuck,
Sounds like from the TABLE FILE SQLOUT, the columns doesn't have Null (MISSING) values
Try something such as the following (I don't know your field format, so you may need to adjust)
SET NODATA=0
DEFINE FILE SQLOUT
NPRPTY_CD /A10 = IF PRPTY_CD EQ MISSING THEN 'MISS' ELSE IF PRPTY_CD EQ '' THEN 'BLANK' ELSE PRPTY_CD;
END
TABLE FILE SQLOUT
PRINT
PRPTY_CD AS 'Property Code'
NPRPTY_CD AS 'Test 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'
….
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
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013