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 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
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] )
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 is basically used to replace “null” by our own keywords like – N/A, Not Available in Jasper Reports.
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.
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.
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
NVL(to_char(DATE_FIELD, ‘DD-MON-YYYY’), ‘Not Available’) as DATE_FIELD