Queries for faster development

Concatenation of column values :

I want to concatenate one column values and get it as an aggregated result from database,

For Eg :

EMAIL_ADDRESS 

kiran.joshi@darkhorseboa.com
j.kiran809@gmail.com
j.kiran@alahli.com

Result should be something like this

<to><address><emailAddress>kiran.joshi@darkhorseboa.com</address><address><emailAddress>j.kiran809@gmail.com</emailAddress></address><address><emailAddress>j.kiran@alahli.com</emailAddress></address></to>

This can be achieved in SQL Server using STUFF function and the link can be found here

http://learnings.joshikiran.com/2014/10/how-to-use-stuff-keyword-in-microsoft.html

In Oracle it can be achieved by,

SELECT 
  '<to>'||LISTAGG('<address><emailAddress>'||EMAIL_ADDRESS||'</emailAddress></address>','')
    WITHIN GROUP (ORDER BY COLUMN3)||'</to>' AS ALIAS_ID
FROM TABLE1
WHERE COLUMN2 = 'sample' AND EMAIL_ADDRESS IS NOT NULL;

Note : This works for Oracle version 11g R2 and higher only.

Credits : PadmaPriya Iyengar
----------------------------------------------------------------------------------------------------------------------

Get Amount in words from query : SQL Developer

Below query helps you get the amount in words and also in camel case

SELECT INITCAP(TO_CHAR(TO_DATE(ROUND(AMOUNT),'J'),'JSP')) AS AMOUNT_WORDS_CAMEL_CASE FROM DH_EXPLORATION;

Below screenshot is self explanatory,

In the query mentioned, TO_DATE is used to get the amount in words and INITCAP is used to get the amount in words as camel case.

Learnings : From GIB  

How to change oracle user's password if it is expired

Problem statement : I am trying to connect to database using sql developer and it throwed an error saying "the password has expired" and I find it really difficult to change the password from oracle homepage (ver 11).

Resolution : Open the oracle client and connect using your username and password.  It will automatically ask for change password.

Strange thing is there are no password policies at all. You can keep the old password itself for new password :P

How to read auto generated key in Java

Database : SQL Server 2008

insertSuccessFileObj = new ();
insertSuccessFileObj.setFILE_CODE("DASHBOARD");
insertSuccessFileObj.setFILE_TYPE("MARS_DASHBOARD_DETAILS");
insertSuccessFileObj.setFILE_NAME(fileName);
insertSuccessFileObj.setFILE_PATH(filePath + "\\" + YEAR + "\\" + MONTH + "\\"+ fileName);
insertSuccessFileObj.setUPLOADED_DATE(new Date());
insertSuccessFileObj.setMONTH(MONTH);
insertSuccessFileObj.setYEAR(Double.valueOf(YEAR));
insertSuccessFileObj.setCREATED_ON(new Date());
insertSuccessFileObj.setCREATED_BY(BSF.getUser().substring(BSF.getUser().indexOf("=") + 1, BSF.getUser().indexOf(",")));
insertSuccessFileObj.insert();
double fileID = insertSuccessFileObj.getFD_ID(); //fileID returned would be 0.0

The above is a small sample of code which inserts record in a table called MARS_FILE_DETAILS in which FD_ID is a column which is auto generated in database which is of type Double. 

Requirement is, we want to insert record through program and read the FD_ID (auto generated field) and return it as response. This is not achievable using the above sample piece of code because the java layer will know about the whereabouts of primary key only after committing the transaction. Hence we need to commit through program and get the FD_ID. This is done using below line

BSF.getObjectManager()._commitTransactionDirect(true); 

Hence the above sample would be modified as below

MARS_FILE_DETAILS insertSuccessFileObj = new MARS_FILE_DETAILS();
insertSuccessFileObj.setFILE_CODE("DASHBOARD");
insertSuccessFileObj.setFILE_TYPE("MARS_DASHBOARD_DETAILS");
insertSuccessFileObj.setFILE_NAME(fileName);
insertSuccessFileObj.setFILE_PATH(filePath + "\\" + YEAR + "\\" + MONTH + "\\"+ fileName);
insertSuccessFileObj.setUPLOADED_DATE(new Date());
insertSuccessFileObj.setMONTH(MONTH);
insertSuccessFileObj.setYEAR(Double.valueOf(YEAR));
insertSuccessFileObj.setCREATED_ON(new Date());
insertSuccessFileObj.setCREATED_BY(BSF.getUser().substring(BSF.getUser().indexOf("=") + 1, BSF.getUser().indexOf(",")));
insertSuccessFileObj.insert();
BSF.getObjectManager()._commitTransactionDirect(true);
double fileID = insertSuccessFileObj.getFD_ID();

How to use STUFF keyword in Microsoft SQL Server

Usage of STUFF in Microsoft SQL Server 

General syntax of STUFF in SQL Server is 

STUFF ( character_expression , start , length , replaceWith_expression )

You can refer the details from below link
http://msdn.microsoft.com/en-IN/library/ms188043.aspx

SELECT DISTINCT COMMISION_NUMBERS = 
STUFF((Select ','+COMMISION_NUMBER from MARS_ALLOCATION_DATA where MONTH='JUN' and YEAR=2014
 FOR XML PATH('')),1,1,'') FROM MARS_ALLOCATION_DATA

Working with NULLABLE fields in SQL Queries

This post describes possible problems and resolutions on how to "Work with NULLABLE fields in SQL Queries"

Assumptions :

Retrieving column information from a table with some search criteria

Table Name : MARS_ALLOCATION_DATA
Columns : ALLOCATION_NUMBER, ZONE_CODE and ZONE_DESCRIPTION

Sample Query :

SELECT ALLOCATION_NUMBER,ZONE_CODE,ZONE_DESCRIPTION FROM MARS_ALLOCATION_DATA
WHERE MONTH='JUN' AND YEAR=2014

Retrieves ALLOCATION_NUMBER, ZONE_CODE and ZONE_DESCRIPTION from table mentioned whose MONTH is 'JUN' and YEAR is 2014

We wanted to add one more condition as to "Retrieve those records in which ZONE_CODE and ZONE_DESCRIPTION are not same hence we add one more extra condition in query as 
' AND ZONE_CODE != ZONE_DESCRIPTION' which ideally should give the response we deserve but it would give a response which is shown below

Where as ideally a record with ZONE_CODE as 'S' and ZONE_DESCRIPTION is NULL should be retrieved by adding the filter in the query as ZONE_CODE != ZONE_DESCRIPTION. 

Reason :

When adding a search filter which involves NULLABLE fields the filter won't work if one of the columns have NULL in the data as even NULL is not equal to NULL, hence resulting in the response which involves only non-null records in the two columns which are used for filter.

In Such cases it is suggested to add the filter as 
'ISNULL(ZONE_CODE,'') != ISNULL(ZONE_DESCRIPTION,'')'

Hence entire query will be changed accordingly,

SELECT ALLOCATION_NUMBER,ZONE_CODE,ZONE_DESCRIPTION FROM MARS_ALLOCATION_DATA
WHERE MONTH='JUN' AND YEAR=2014 AND ISNULL(ZONE_CODE,'') != ISNULL(ZONE_DESCRIPTION,'')

Which results in the below expected response,