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,

Transaction Object is null : Coboc Browser : Problem installing isvps which has DTs

Issues related to this:

·         Isvps related to CoBOC Content will not be installed as CoBOC browser will not be functioning properly.

·         When opening CoBOC Browser we get an error “TransactionObject is null” and in the log it will be “invalidTransactionObject”.

Possible reason:

The port number of CoBOC container is not in sync with Monitor container hence even after restart we could not solve this issue. So as a resolution we have created a clone of CoBOC container (port number will be changed automatically and it will create proper connection with Monitor service container) and stopped the main container and let the copy of coboc container in started state (assuming the algorithm the CoBOC service group is in failover mode). Then the issue has gone.