Column data truncated with FULL OUTER JOIN

Database
Enthusiast

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)

1 REPLY
Teradata Employee

Re: Column data truncated with FULL OUTER JOIN

Hey

can you please share -->  (DERIVED TABLE) .

I have tried the same and sql is giving correct result.

Thanks