How to Use Decode, NVL & to_char in Jasper Report (with screenshot)

By | October 3, 2017

Previously, we have discussed – How to Add QR Code in Jasper Reports. Today we are going to discuss NVL, Decode & to_char in Jasper Reports. Decode, NVL & to_char are the functions having different us in Jasper Reports, Tables etc. Here are the steps for using Decode, NVL & to_char in Jasper Reports. We can also use these functions together like – to_char & NVL. NVL, Decode & to_char functions with their syntax are discussed below. Please share this article with your friends on Facebook, Twitter etc.

Decode Function:

Decode Function is used to decode the flags used in the database. Suppose we have used two flags (code) ie. N & Y.

N = NO (N may be used for No)
Y = YES (Y may be used for Yes)

When we fetch data from database in a table, Only the flag will be displayed, means only N or Y will be displayed in the table. That may be very difficult to understand for the users. Hence, we use Decode function to overcome this problem

Syntax:-

decode(VERIFICATION_REQUIRED, ‘N’, ‘NO’, ‘Y’, ‘YES’) as VERIFICATION_REQUIRED

Here VERIFICATION_REQUIRED = Actual Field Name to be decoded.    Here ‘N’ is decoded to ‘NO’ & ‘Y’ is decoded to ‘YES’.

VERIFICATION_REQUIRED = Name to be displayed after decode (may be same as Actual Field Name or we can give some other relevant name)

In the result table, it will display No & Yes instead of N & Y.

Decode Function in Oracle / PLSQL

The Oracle/PLSQL DECODE function has the functionality of an IF-THEN-ELSE statement. The syntax for the DECODE function in Oracle/PLSQL is given below.

DECODE( expression , search , result [, search , result]… [, default] )

Where

Expression:-  The value to compare. It is automatically converted to the data type of the first search value before comparing.

Search:–  The value that is compared against expression. All search values are automatically converted to the data type of the first search value before comparing.

Result:–  The value returned if the expression is equal to search.

Default:–  Optional. If no matches are found, the DECODE function will return the default. If default is omitted, then the DECODE function will return NULL (if no matches are found).

NVL Function

NVL function is basically used to replace “null” by our own keywords like – N/A, Not Available in Jasper Reports.

Syntax:

NVL(CUSTOMER_NAME, ‘N/A’) 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.

TO_CHAR Function

This function is basically used to decode date in Jasper Reports. If we fetch data from the database to table, the date will be displayed in the default format. The 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(DATE_FIELD, ‘DD-MON-YYYY’) AS  DATE_FIELD

DATE_FIELD = column name

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

or we can use

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

Using NVL & to_char functions together

Syntax:

NVL(to_char(DATE_FIELD, ‘DD-MON-YYYY’), ‘Not Available’) as DATE_FIELD

Leave a Reply