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

1 comment:

7tj5went0f said...

A water park and one of South Korea's largest retail, meals and beverage complexes are additionally planned. Expect about 2,000 resort rooms, luxury villas, and Jeju's first 6-star resort and destination spa. Las Vegas casino resorts have been lengthy recognized for his or her entertaining shows, endless casino 안전놀이터 motion and bright lights. Things occur all the time basis}, might have|you may have|you would possibly have} a minor problem or question at hand, and with out immediate help, you could become caught in your play sessions.