How to Use NVL, Decode & to_char in Jasper Reports - By Wide Info Blog

This blog - Wide Info Blog provides blogger tips, tricks & tutorials. Previously we have discussed - How to Add QR Code in Jasper Reports. Today we are going to discuss about NVL, Decode & to_char in Jasper Reports.

NVL Function :: NVL function is basically used to replace "null" by our own keywords like - NA, Not Available in Jasper Reports.

Syntax::  NVL(CUSTOMER_NAME, 'NA') as CUSTOMER_NAME,   

CUSTOMER_NAME  is the column name on which NVL function is applied. If this column will be blank or null the 'NA' will be displayed in place of null or blank in results. we can also change 'NA' to as per our requirement.

Decode Function ::  Decode Function is used to decode the flags used in the database. Suppose we have used 

N = NO

Y = YES

But default results will show only N & Y, which may be difficult to understand for others. So, we use the decode function like-

Syntax ::  decode(VERIFICATION_REQUIRED, 'N', 'NO', 'Y', 'YES')VERIFICATION_REQUIRED

VERIFICATION_REQUIRED is the Column name to be decoded.  Here 'N' is decoded to 'NO' & 'Y' is decoded to 'YES'.

TO_CHAR Function :: This function is basically used to decode date in Jasper Reports. default format of date in jasper reports is like --  1/16/15 12:00 AM

So to avoid this we use the to_char function.

Syntax ::  to_char(FILE_DATE, 'DD-MON-YYYY')FILE_DATE

FILE_DATE = column name

DD-MON-YYYY =  Date format (10 - DEC - 2015)

or we can use

DD-MM-YYYY  =  Date format (10 - 12 - 2015)