While do cast (Decimal to Char): follow me!!!

As a part of my task; I was making query something like below,

select cast (city_code as char(10)), city_name||’_’ ||city_type as CITY_INFO

From CITY

I wondered why my data  for City_Code look like this:

City_Code	CITY_INFO
10134.	COPENHAGEN_CYCKLE
101.	STOCKHOLM_TOG
3497.	DHAKA_RICKSH
2789.	LISBON_TOUR

City_code ended with ‘.’ Then looked carefully the original datatype for the column (CITY_CODE), it’s decimal (18, 0). That is the reason why it’s giving the problem.

Googled it and found some solution:

select cast(cast(city_code as integer) as char(10)), city_name||’_’ ||city_type as CITY_INFO

From CITY

Since the column is decimal, it’s first cast to integer and then cast to char. It works perfectly. But senior developer who reviewed the code asked not to two time casting rather use trim function to do this.

Fair enough!! Got motivation to do something else J

Then I have tried to implement the code like below:

select TRIM(TRAILING '.' FROM (cast(UNQ_ID_SRC_DEC as char(10))))

,city_name||’_’ ||city_type as CITY_INFO

 From CITY

I have trimmed with trailing ‘.’ But result did not change!!

City_Code	CITY_INFO
10134.	COPENHAGEN_CYCKLE
101.	STOCKHOLM_TOG
3497.	DHAKA_RICKSH
2789.	LISBON_TOUR

I was really curious to know where the problem is. After investigating a little more time on it, I made the query like below:

select TRIM(TRAILING '.' FROM (cast(UNQ_ID_SRC_DEC as varchar(10))))

,city_name||’_’ ||city_type as CITY_INFO

 From CITY

Yaahuuu!! It works as I expected. ‘.’ has been removed.

City_Code	CITY_INFO
10134	COPENHAGEN_CYCKLE
101	STOCKHOLM_TOG
3497	DHAKA_RICKSH
2789	LISBON_TOUR

I just changes cast data type from char to varchar and then it works fine!!

Hope my experience will  help some of you sometime!!!

Tricks: UNION and UNION ALL

While you are making union in Teradata it’s very important to follow below rules:

1) Select statement should have equal number of columns

For example:

select city_code, city_name, city_type

From CITY

union

select EMP_CITY_CODE as city_code, EMP_CITY as city_name, EMP_city_type as City_type

from CITY_EMP

2) Type should be same

If data type is different; then it should be cast and then make union, if you don’t define/cast the type then value will be truncated. So when you define type consider maximum size of data type.

For example:

select cast (city_code as char(10)), city_name, city_type

From CITY

union

select cast(EMP_CITY_CODE as char(10)) as city_code, EMP_CITY as city_name, EMP_city_type as City_type

from CITY_EMP

3) Where clause at UNION

At the time you are selecting if you cast a type , always remember to do the same when you filter at where clause.

For example:

insert DW_EMP_CITY (city_code,city_name,city_type)

select cast (city_code as char(10)), city_name, city_type

From CITY

union

select cast(EMP_CITY_CODE as char(10)) as city_code, EMP_CITY as city_name, EMP_city_type as City_type

from CITY_EMP

where not exists (select 1 from DW_EMP_CITY as EDW_CITY where EDW_CITY.city_code=cast(CITY_EMP.EMP_CITY_CODE as char(10))

4) When to use UNION/UNION ALL

If you want to avoid duplicates then use ‘UNION’ otherwise you use ‘UNION ALL’.