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     [CLOSED] SET NODATA not working
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] SET NODATA not working
 Login/Join
 
Member
posted
Hey all,

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,
 
Posts: 5 | Registered: June 04, 2019Reply With QuoteReport This Post
Gold member
posted Hide Post
Hello,

Can you try holding SQLOUT first and then apply SET NODATA=0.

Thank you.


WebFOCUS 7.X.X, 8.X.X
Windows, All Outputs
 
Posts: 71 | Registered: February 02, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2161 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SET NODATA not working

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