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:
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.
Post a Comment