All Forums Database
Abdulaziz 10 posts Joined 10/11
04 Nov 2015
Column data truncated with FULL OUTER JOIN

I am doing a FULL OUTER JOIN on 2 tables. Answerset I am getting is truncating leading '0's for one of the columns. Is there a special consideration/casting that we have to do when we do a FULL OUTER JOIN? The results are coming out fine when i am doing INNER JOIN(no 0 truncation)

 

I checked the data type of the truncated field from table and answerset and it all shows VARCHAR(6).

 

Can anyone please suggest how we can get this resolved?

 

Example:

 

Data:

 

TABLE1(VIEW)

Col1 Col2 Col3

Integer Varchar(3) Varchar(6)

12345 003 105026

45678 003 092356

 

 

TABLE2(Derived table)

Col1 Col2

Integer Varchar(3)

12345 003

45678 003

 

 

Below query truncating leading '0's for one of the columns

 

Select * from TABLE1

FULL OUTER JOIN

(DERIVED TABLE) TABLE2

ON TABLE1.Col1=TABLE1.Col1

and TABLE1.Col2=TABLE2.Col2;

 

 

Answerset:

Col1 Col2 Col3

Integer Varchar(3) Varchar(6)

12345 003 105026

45678 003 92356 <--(leading 0 gets truncated)

 

 

Below query working fine

 

Select * from TABLE1

INNER JOIN

(DERIVED TABLE) TABLE2

ON TABLE1.Col1=TABLE1.Col1

and TABLE1.Col2=TABLE2.Col2;

 

Answerset:

Col1 Col2 Col3

Integer Varchar(3) Varchar(6)

12345 003 105026

45678 003 092356 <--(No truncation)

Regards, Abdulaziz Shaikh
punitgupta87 4 posts Joined 03/14
05 Nov 2015

Hey
can you please share -->  (DERIVED TABLE) .
I have tried the same and sql is giving correct result.
 
Thanks

You must sign in to leave a comment.