Pages

Wednesday, August 6, 2014

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!!!

Tuesday, August 5, 2014

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’.