OVER(partition by )

Partition is explained clearly in the following two links.

Link-I

Link-II

Left Outer Join

select parentrequest.bankname, parentrequest.amount, parentrequest.bofa_ref#, parentrequest.gtemfilenum ' - ' parentrequest.sequencenumber as gtemfilenum, parentrequest.status, parentrequest.approved_bydate, parentrequest.maximum_maturitydate, childrequest.gtemfilenum ' - ' childrequest.sequencenumber as gtemfilenum, childrequeststatus.display as status, childrequest.creationdate, childrequest.maximummaturitydate as maximum_maturitydatefrom own_gtem.requestdetails childrequest join own_gtem.requeststatusdescription childrequeststatus on childrequest.requeststatuskey = childrequeststatus.requeststatuskey, ( select bd.bankname, rd.amount, rd.bacrefnum as bofa_ref#, rd.gtemfilenum, rd.sequencenumber, rsd.display as status, rd.approvedbydate as approved_bydate, rd.maximummaturitydate as maximum_maturitydate from own_gtem.requestdetails rd join own_gtem.requeststatusdescription rsd on rd.requeststatuskey = rsd.requeststatuskey left outer join own_gtem.bankfacilityconnection bfc on rd.requestdetailskey = bfc.requestdetailskey left outer join own_gtem.bankfacility bf on bfc.bankfacilitykey = bf.bankfacilitykey left outer join own_gtem.bankdetails bd on bf.bankdetailskey = bd.bankdetailskey where rd.requeststatuskey=9 ) parentrequestwhere parentrequest.gtemfilenum = childrequest.gtemfilenum and childrequest.requeststatuskey in (6, 7, 8)

Usage of Dual table

If the select query involves fetching data from multiple tables then we use dual table.

AND 0=(SELECT COUNT(*) FROM DUAL WHERE TBUONE.TBUNAME = 'TOC-NY' AND RTT.CODE = 'LACP-OAP' AND CD1.COUNTRYCODE = 'KR' AND RD.AUTOAPPROVEDFLAG = 'N' AND RD.AMOUNT < 500000)

Oracle Tutorials

SQL Tips and Tutorials

Create table concepts.

DROP TABLE OWN_GTEM.USERROLETYPES CASCADE CONSTRAINTS;

cascade constraints
Deletes all foreign keys that reference the table to be dropped, then drops the table.

ALTER TABLE OWN_GTEM.USERROLETYPES DROP PRIMARY KEY CASCADE;

DROP PRIMARY KEY CASCADE
Dropping constraints generally requires knowing their names (only in the special case of primary or unique key constraints can you drop them without specifying their names). Thus, it is always a good idea to name all your constraints.

COMMENT ON TABLE OWN_GTEM.USERSEARCHVALUES IS 'User Search Selections For Reload on Dashboard pages';

COMMENT ON TABLE
The comment statement allows to store some comments about tables, views or columns in the data dictionary.

CREATE INDEX OWN_GTEM.PK_USERSEARCHVALUES ON OWN_GTEM.USERSEARCHVALUES(USERID, DASHBOARD)
LOGGING
TABLESPACE
GTEM_D
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE INDEX

TO_DATE Function in SQL

How to find the names of employees who joined between 10th and 20th of August 2008.

SELECT empName FROM emp WHERE joining_date between to_date ('2008/08/10', 'yyyy/mm/dd')AND to_date ('2008/08/20', 'yyyy/mm/dd');

Link-1 and Link-2 helps us understand, to_date and to_char functions better.

ORDER BY TO_DATE(TO_CHAR(APPROVEDBYDATE,'MM/DD/YYYY'),'MM/DD/YYYY')

How to sort null values first using ORDER BY

This link helps to understand the sorting of null values better.


CASE WHEN(rd.riskdistributionflag LIKE 'Y' AND rd.fundingstatustypekey=2)
THEN 'RDU'
WHEN (rd.riskdistributionflag LIKE 'Y' AND rd.fundingstatustypekey=1)
THEN 'RDF'
END AS RDUF,

ORDER BY nvl(RDUF,'AAA')

The above query ensures the NULL rows are ordered first and then the data comes later.

This link helps to understand the sorting order

Finding Duplicates in a table

Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

ROWNUM

Here is the link that explains about ROWNUM concept.

Functions in Oracle

Here is the link to Oracle functions like

1. Trim
2. Length
3. Replace
4. Substr
5. Upper
6. Coalesce
7. Decode
8. Nvl/ Nvl2
9. To_Date
10 To_Number

UNION keywork

Here is a tutorial on how to use union in sql queries.

Sequence Number in J2EE/Oracle web app

In a medium to large scale web app we have different modules with relevant tables.
In those tables we have sequence number which has to be filled in automatically. So to create this kind of auto generated numbers we use Sequences in Oracle.


public static final Class CLASS_NAME = PKSequenceUtility.class;
public static Integer populateApplnData(String tableName){
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
int seqNum = 0;
try {
con = DBUtility.getConnection();
// Constants.SCHEMA_NAME+".sq_"+tableName+".nextval from dual ==> "own_abc.sq_requesttable.nextval from dual"
String sql = "select "+Constants.SCHEMA_NAME+".sq_"+tableName+".nextval from dual";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
seqNum = rs.getInt(1);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
finally
{
DBUtility.closeResultSet(rs);
DBUtility.closeStatement(ps);
DBUtility.releaseConnection(con);
}
return (seqNum);
}

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.

Deleting multiple values using a single query

We have a table called userroles with 2 columns 1. userid and 2. userroletypekey

Userid is string field containing different user ids. userroletypekey is an int field with values from 1 to 8.

Now I want to retain only a single roletypekey for a particular user and delete the rest of userroletypekeys. Frame the query of this requirement.

delete from userroles where userid = 'NBK4HKA' and userroletypekey in (1,2,4,5,6,7,8)
commit

SQL Basic's - Tutorial

Tutorial Link - I

Tutorial Link - II