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,

Hello,

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

Thank you.

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'
….