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

SQL Left Join: Condition on where clause or on clause

As a database developer, we know about SQL left, right or inner join clearly. But when it comes to left join or right join with condition putting on where clause or on clause can make big  difference that we sometimes overlook or get panic why those making difference.

I will go directly to the example, e.g. we have Account and Customer table and we want all the accounts those are exist in Account table and also consider if any customer is added on '2013-05-27', we take him as well.

Here we have Account and Customer table and data look like below:

Account
Account_No	Status	Eff_DT	Customr_ID
123	actv_	21-05-2014	301
124	actv_	22-05-2014	307
125	actv_	23-05-2014	303
Customer
Customer_ID	Customer_Name	Eff_DT
301	john	21-05-2014
302	san	21-05-2014
303	paul	22-05-2014
304	helle	23-05-2014
305	jon	24-05-2014
SQL Query :
select Account_no, status,A. eff-dt, A.customer_id, Customer_Name  from ACCOUNT as A
join customer as C on A.customer_id=C.customer_id
Result Set:
Account_No	Status	Eff_DT	Customr_ID	Customer_Name
123	actv_	21-05-2014	301	john
124	actv_	22-05-2014	307	null
125	actv_	23-05-2014	303	paul
Changes the query with 'on' clause:
select Account_no, status,A. eff-dt, A.customer_id, Customer_Name, C.EFF_DT as Customer_EFF_DT  from ACCOUNT as A
join customer as C on A.customer_id=C.customer_id
and C.eff_dt= date '2013-05-24'
Result will look like:
Account_No	Status	Eff_DT	Customr_ID	Customer_Name	Customer_EFF_DT
123	actv_	21-05-2014	301	john	null
124	actv_	22-05-2014	307	null	null
125	actv_	23-05-2014	303	paul	null
Changes the query with 'Where' clause
select Account_no, status,A. eff-dt, A.customer_id, Customer_Name, C.EFF_DT as Customer_EFF_DT  from ACCOUNT as A
join customer as C on A.customer_id=C.customer_id
where C.eff_dt= date '2013-05-24'
Result will return nothing
It means where clause filter after join has established. When we did same condition   C.eff_dt= date '2013-05-27' on clause it was still under left join on condition
and that returns you data as how left join work. But where clause work after making join,  if we look at the last result, consider we are implying where clause on the last record set.