Functions

INSTR

This function helps to search in a given string.

INSTR(trim(cp.PARAMETERVALUE), trim(ft.CODE) || ',' ) > 0)

Here || is NOT or function. It or only in Java. In SQL it is append.

COALESCE

The coalesce function returns the first non-null expression in the list.

Change Log Functionality

Change Log Functionality:

We shall have a button called change log in the view screen and ONCLICK of this button, we should be able to see all the modifications to each column in a table format. For example in TBU a new record is created and it is modified 10 times in the past 10 days with different tbu name. On click of Change Log button we should be able to see all the 10 changes in a table format with all the changes in a new value/old value format.

In the DAO layer we fetch all the related values from the sql query and populate in a bean and add all the beans to a List.

TBU Change Log:

We have a module known as TBU. We have related tables like TBU, TBUDIVISION, TBUISUSLABELS, TBU_ADT (Audit table).

Columns in TBU table.

tbukeysystemstatuskey (1. active 2. cancel)tbudivisionkey
tbuname
tbuid
tbuisuskey
statusasofdate
lastmoduser
lastmoddate

When the user creates a new record a row is added to TBU table and TBU_ADT table (through triggers). TBU_ADT is an audit table and this table contains the same columns like that of TBU table. The only difference is in TBU table we have tbukey as primary key and in TBU_ADT table the primary key is a composite key (tbukey and lastmoddate). Hence the combination of tbukey and lastmoddate should be unique.

If the user does any modification to the newly created record in the TBU table from the front end (JSP page), a new row is created in TBU_ADT table with the current date as lastmoddate and same tbukey.

Hence for each modification to the TBU screen a new row is added to TBU_ADT with the latest modification and current date as modified date.

For the given TBU=88 we get only 4 rows of data with different LastModDate

As you can see we are selecting both TBU_NEW.LASTMODDATE and TBU_OLD.lastmoddate where as we are passing TBUKEY only to TBU_NEW. So the query is comparing each of the 4 rows that are selecting from TBU_NEW.TBUKEY = 88 to all other records that are fetched using TBU_OLD.lastmoddate.

select count(*) from OWN_GTEM.TBU_ADT ==> 159

So the final result shows up (159*4) 636 rows as output.

To filter the above snapshot results we include a condition like

AND TBU_NEW.TBUKEY = TBU_OLD.TBUKEY

to the end of the query. So this additional condition ensures same 4 rows of data are fetched from both TBU_NEW and TBU_OLD and compared with each other. Hence in the output result we get 16 rows of data.

To filter the results of snapshot 3 we include a condition like

AND TBU_NEW.LASTMODDATE > TBU_OLD.LASTMODDATEThis condition ensures that each LastModDate is compared with other 3 dates and the greatest is shown for new lastmoddate. The snapshot speaks for itself.

To understand how we get the above results in an orderly fashion we need to look into snapshot 6.

As you can see in the 6th snapshot speciallastmoddate will return the max of the lastmoddate that satifies the condition TBU_NEW.LASTMODDATE > TBU.LASTMODDATE

We then pass that max date to
AND TBU_OLD.LASTMODDATE =which helps to filter the 2, 4 and 5th rows from snapshot 4.

EXISTS Keyword

The EXISTS condition is considered "to be met" if the subquery returns at least one row.

This link helps us to understand EXISTS better.

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