NVL function

Tutorial Link

Space between names from database

TRIM(GU.FIRSTNAME), TRIM(GU.LASTNAME)

Result: abc,xyz

Requirement: I want both first and last name with space in-between.
Question: How to get this in SQL query.

TRIM(GU.FIRSTNAME << concatenation operator (2 straight lines) >> ' ' << concatenation operator >> GU.LASTNAME)

Tutorial Link

to_date, to_char, to_number

TO_DATE

Example:
TO_DATE(:SUBMISSIONDATE,'MM/DD/YYYY')

:SUBMISSIONDATE -- This is an input to the sql query which comes from java layer.

Tutorial Link

TO_CHAR

If we want to convert a date or number to string then we use to_char

Tutorial Link

How to compare 2 dates ?

TO_DATE(TO_CHAR(RD.SUBMISSIONDATE,'MM/DD/YYYY'),'MM/DD/YYYY') LIKE TO_DATE(:SUBMISSIONDATE,'MM/DD/YYYY'))

RD.SUBMISSIONDATE will return a timestamp (which includes hours, minutes and seconds). So first we convert a timestamp to a string date with the specified format and pass it to to_date as input, then we compare this date with other date using LIKE keyword.

For Greater than and Less than we can use regular symbols like < and >

AND TBU_NEW.LASTMODDATE > TBU_OLD.LASTMODDATE

We can also use equal to operator to compare dates

RD.MAXIMUMMATURITYDATE =RD_ADT.MAXIMUMMATURITYDATE


TO_NUMBER

Tutorial Link

ROWNUM Tutorial

Basic Tutorial:


ROWNUM for Pagination:

Designing the search query for 3 drop downs containing the same values

We have 2 search modes. Normal and Advanced. In Normal mode we have only one drop-down with 10 search criteria’s. In advanced mode we have 3 drop-downs with the same search criteria’s. We have this advanced mode to filter the search values.

Now that we have a basic understanding of our requirement we shall go ahead and design the implementation.

We have 3 drop-downs to select the search criteria and 3 textboxes to enter the values. Once the user selects the search criteria and enters values, search values are added to a HashMap with the search criteria as key and user condition as value. Before we put the values in a map we trim the user conditions to remove extra spaces like

criteriaSelectedMap.put(criteriaSelected1, valueSelected1.trim());

The keys in the map are nothing but the search criteria which are fetched from database, means those values are pre-defined and known to us before hand.

We then do a null check and get the value from the map using the pre-defined key and check for wild card search and finally store it in a bean as a property. We follow the same approach for all the 10 drop-down values and store the user search condition in one of the 10 property of the bean.

Wild card search: Wild card search is nothing but using * for searching in UI (JSP Pages). In the back end (database) all the * has to be replaced with % as we use this (%) symbol for searches.

Example:
Select name from employee where name like ‘s%’;
Here we are searching for names that start with s.

Select name from employee where name like ‘%s’;
Here we are searching for names that end with s.

RequestSearchBean objRequestSearchBean = new RequestSearchBean();

if (searchCriteriaMap != null && !searchCriteriaMap.isEmpty()) {
String countryName = searchCriteriaMap.get(SEARCH_COUNTRY);
}

if (countryName != null) {
objRequestSearchBean.setCountryName(
frameWildCardSearch (countryName));
}

public static String frameWildCardSearch(String criteria)
{
if (criteria == null) return criteria;
if (criteria.contains("*"))
{
criteria = criteria.replace('*','%');
}
return (criteria);
}

We will now design the JDBC part.

preparedStatement = objConnection.prepareStatement(sqlQuery);

ps.setString(1, objRequestSearchBean.getCountryName());
ps.setString(2, objRequestSearchBean.getCountryName());
ps.setString(3, objRequestSearchBean.getBankName());
ps.setString(4, objRequestSearchBean.getBankName());

Query part:

AND RD.REQUESTDETAILSKEY = BFC.REQUESTDETAILSKEY(+)
AND BFC.BANKFACILITYKEY = BFTY.BANKFACILITYKEY(+)
AND BFTY.FACILITYTYPEKEY = FT.FACILITYTYPEKEY(+)

AND (1 = NVL2 (:COUNTRYNAME, 0, 1) OR UPPER (CD1.DISPLAYNAME) LIKE UPPER(:COUNTRYNAME))
AND (1 = NVL2 (:BANKNAME, 0, 1) OR UPPER (BD.BANKNAME) LIKE UPPER(:BANKNAME))

This is how we pass all the 10 user search conditions to the query.

NVL2
=====

Syntax:
NVL2( string1, value_if_NOT_null, value_if_null )

Example:
select NVL2(supplier_city, 'Completed', 'n/a') from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.

So NVL2 function will return either 1 or 0. If it returns 1 then it is understood that the input value is null and the condition of (1 = 1) is satisfied and the control won’t go the next part that’s the OR part.