/*
			DEBUGGING COLUMN LENGTH ISSUE
			-----------------------------
			Author   JP Vijaykumar
			Date	 Dec 7th 2008
			Modified Jan 4th 2009

In one application, we stumbled upon the following issue.
For some reason, the same string values are stored of different lengths
in two different tables. As such the same matching rows from the application 
tables could not be displayed through an equi join from two tables.

The version of the Oracle database is (the version is provided by the query output in the script).
*/
 

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE    8.1.6.0.0       Production
TNS for Solaris: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production


/*
Using functions on the tables' columns, did not help in selecting
matching records from the tables.
*/



select count(*) from (
select a.FTP_USER_id
from scott.FTP_USER a,scott.FTP_SAMAR b
where a.user_id=b.user_id);  

  COUNT(*)
----------
         0


select count(*) from (
select a.FTP_USER_id from scott.FTP_USER a, scott.FTP_SAMAR b
where upper(a.user_id)=upper(b.user_id));  

  COUNT(*)
----------
         0


select count(*) from (
select a.FTP_USER_id from scott.FTP_USER a, scott.FTP_SAMAR b
where trim(a.user_id)=trim(b.user_id));  

  COUNT(*)
----------
         0

select trim(upper(user_id)) from FTP_USER
where trim(upper(user_id)) in
(select trim(upper(user_id)) from scott.FTP_SAMAR ); 

no rows selected

/*"no rows selected", even after using the functions trim, rtrim & ltrim, 
upper on the user_id column in the where clause.


A manual verification of the two tables for matching values on
user_id column values found some records.


Let us find the length of the string value 'TRUE_NEWSFORMAL', that existed
in both the application tables.*/

select length('TRUE_NEWSFORMAL')  from dual;

LENGTH('TRUE_NEWSFORMAL')
-------------------------
                       15

/*Let us verify the tables' structure:*/


desc scott.FTP_USER
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 FTP_USER_ID                               NOT NULL NUMBER(38)
 USER_ID                                            VARCHAR2(56) <--


desc scott.FTP_SAMAR
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 USER_ID                                            VARCHAR2(33) <--


select table_name, column_name,data_type,data_length
  from dba_tab_columns
  where table_name in ('FTP_USER','FTP_SAMAR')
  and column_name in ('USER_ID');


TABLE_NAME COLUMN_NAME DATA_TYPE   DATA_LENGTH
---------- ----------- ---------- ------------
FTP_SAMAR  USER_ID     VARCHAR2             33
FTP_USER   USER_ID     VARCHAR2             56

/*
The user_id column data_type is identical in both the tables - VARCHAR2. 

The tables' user_id column length diffrence of VARCHAR2(56) and VARCHAR2(33) 
will not make any difference in comparing the stored user_id column values. 

If the user_id column value in the application tables is identical,
the records from the tables can be joined in an equi join.


Let us find the length of user_id column value('TRUE_NEWSFORMAL') from our tables.
*/

select length(user_id) length, user_id  from scott.FTP_SAMAR
    where rownum = 1;  

    LENGTH USER_ID
---------- ---------------------------------
        16 TRUE_NEWSFORMAL

		
/*Here our string of 15 characters length is stored as a 16 character string in one table.*/

select length(user_id) from scott.FTP_SAMAR
where ltrim(rtrim(upper(user_id))) = 'TRUE_NEWSFORMAL';

no rows selected

/*We checked the first and the last characters of the string.*/


select user_id,length(user_id) length,
substr(user_id,1,1) first_char,
substr(user_id,length(user_id),length(user_id)) last_char
from scott.FTP_SAMAR where rownum = 1;   2    3    4

USER_ID                  LENGTH F LAST_CHAR
-------------------- ---------- - --------------------
                             16 T

/*
Here Oracle is appending a blank space to the string variable in the 
table scott.FTP_SAMAR
*/

select length(user_id), user_id  from scott.FTP_USER
   where  user_id = 'TRUE_NEWSFORMAL';  

LENGTH(USER_ID) USER_ID
--------------- --------------------------------------------------------
             15 TRUE_NEWSFORMAL

select user_id,length(user_id) length,
substr(user_id,1,1) first_char,
substr(user_id,length(user_id),length(user_id)) last_char
from scott.FTP_USER where  user_id = 'TRUE_NEWSFORMAL';


USER_ID                  LENGTH F LAST_CHAR
-------------------- ---------- - --------------------
TRUE_NEWSFORMAL              15 T L

/* 
In the second table, the string value ('TRUE_NEWSFORMAL') is correctly stored. 

The length of the same string value is different from these two tables.
As such I used substr function on the column value to prune the extra blank space
to generate a report with an equi join.
*/

select a.FTP_USER_id,a.user_id, b.user_id
from scott.FTP_USER a,scott.FTP_SAMAR b
where a.user_id='TRUE_NEWSFORMAL'
and substr(b.user_id,1,length(b.user_id) -1)='TRUE_NEWSFORMAL'
and a.user_id=substr(b.user_id,1,length(b.user_id) -1);  

FTP_USER_ID USER_ID              USER_ID
----------- -------------------- --------------------
     175948 TRUE_NEWSFORMAL      TRUE_NEWSFORMAL

select a.FTP_USER_id,a.user_id, b.user_id 
from scott.FTP_USER a, scott.FTP_SAMAR b
where upper(a.user_id)=upper(substr(b.user_id,1,length(b.user_id) -1))
and a.user_id = 'TRUE_NEWSFORMAL';  

FTP_USER_ID USER_ID              USER_ID
----------- -------------------- --------------------
     175948 TRUE_NEWSFORMAL      TRUE_NEWSFORMAL

/*	 
Some more constructs to display matching records from the two tables
using an equi join .
*/

 select count(*) from (
select a.FTP_USER_id
from scott.FTP_USER a,scott.FTP_SAMAR b
where a.user_id=b.user_id); 

  COUNT(*)
----------
         0

select count(*) from (
select a.FTP_USER_id
from scott.FTP_USER a,scott.FTP_SAMAR b
where a.user_id=substr(b.user_id,1,length(b.user_id) -1)); 

  COUNT(*)
----------
       393


select a.FTP_USER_id from scott.FTP_USER a,scott.FTP_SAMAR b
where a.user_id='TRUE_NEWSFORMAL'
and b.user_id='TRUE_NEWSFORMAL' ;

no rows selected


select a.FTP_USER_id,a.user_id, b.user_id from scott.FTP_USER a,scott.FTP_SAMAR b
where a.user_id='TRUE_NEWSFORMAL'
and substr(b.user_id,1,length(b.user_id) -1)='TRUE_NEWSFORMAL';  2    3

FTP_USER_ID USER_ID              USER_ID
----------- -------------------- --------------------
     175948 TRUE_NEWSFORMAL      TRUE_NEWSFORMAL


/*	 
The table FTP_SAMAR is populated through sql*loader utility.


This column length issue can be resolved by re-creating the table and
re-loading the table. 

You need to tweek some of the options in the sql*loader controlfile.

Some variations, in resolving the issue:
*/


select length('TRUE_NEWSFORMAL') from dual;

LENGTH('TRUE_NEWSFORMAL')
-------------------------
                       15


select length('TRUE_NEWSFORMAL ') from dual;

LENGTH('TRUE_NEWSFORMAL')
-------------------------
                       16

/*I will use a decode function to prune the extra blank character.*/

select decode(substr(reverse('TRUE_NEWSFORMAL '),1,1),' ',
      reverse(substr(reverse('TRUE_NEWSFORMAL '),2,length('TRUE_NEWSFORMAL '))),
      'TRUE_NEWSFORMAL ') string from dual;  2    3

STRING
---------------
TRUE_NEWSFORMAL

/*
To test my construct is working, I replaced the ' ' with 'L'
and displayed the resultant output from my query.
*/

select decode(substr(reverse('TRUE_NEWSFORMAL'),1,1),'L',
      reverse(substr(reverse('TRUE_NEWSFORMAL'),2,length('TRUE_NEWSFORMAL'))),
      'TRUE_NEWSFORMAL') string from dual;  

STRING
--------------
TRUE_NEWSNORMA


/*Another construct with case function(from Oracle 9i and above):*/
 
select case when substr(reverse('TRUE_NEWSFORMAL '),1,1) =' '
      then reverse(substr(reverse('TRUE_NEWSFORMAL '),2,length('TRUE_NEWSFORMAL ')))
      end string from dual;  2    3

STRING
---------------
TRUE_NEWSFORMAL