We have a table which holds the information about CUSTOMERS and the CONTACT NUMBER of the CUSTOMERS. So in table we have CUST_ID, CUST_NUMBER and CUST_OFF_NUMBER. For few CUSTOMERS, the NUMBER and OFFICE NUMBER is same and for few CUSTOMERS , NUMBER and OFFICE NUMBER is different. Below is the snapshot of the CUSTOMERS table:
The requirement is to fetch output in two columns (CUST_ID, CUST_NUMBER) such that if the CUSTOMER has same CUST_NUMBER and CUST_OFF_NUMBER then only one row should be present for the CUSTOMER however if the CUST_NUMBER and CUST_OFF_NUMBER are different then two rows should be present ; one for CUST_ID,CUST_NUMBER and second for CUST_ID,CUST_OFF_NUMBER. Below is the output which is required from the SQL Query.
Can you write the correct query? Leave your SQL Query as comment below. There may be several ways of getting the desired output. You can check the answer on ANSWERS PAGE too. However for better understanding of Teradata SQL , it is suggested that you leave a comment below with your SQL Query and then check our answer. We may get better and more ways of getting the result.
Hint: Try it via SQL Set Operators
Select cust_Id, cust_number from customer
Union
Select cust_Id, cust_off_number from customer where cust_numbercust_off_number
select cuse_id, (case when cust_num=cust_off_num then cust_num
else cust_off_num end) as cust_num from customer
union
select cuse_id,cust_num from customer
where cust_numcust_off_num
select * from (
select cust_id, case when cust_numbercust_office then cust_office else cust_number end cust_num from customer
union
select cust_id,cust_number from customer) A
order by cust_id;
Select cust_Id, cust_number from customer
Union all
Select cust_Id, cust_off_number from customer where cust_number cust_off_number
SEL CUST_ID , CUST_NUMBER FROM CUSTOMERS
UNION
SEL CUST_ID,CUST_OFF_NUMBER FROM CUSTOMERS;
WITH cust(cust_id,phone_no)
as
(select cust_id,
case x
when 1 then CUST_NUMBER
when 2 then CUST_OFF_NUMBER
end as phone_no
from CUSTOMERS cross join
(select day_of_calendar as x from sys_calendar.calendar
where x between 1 and 2) as dt
)
sel distinct cust_id,phone_no from cust;
select distinct * from
(select cust_id,cust_number from customers
union
select cust_id,cust_off_number from customers
);
SELECT * FROM
(
SELECT DISTINCT id, CUST_NUMBER FROM CUSTOMER
UNION
SELECT DISTINCT ID, CUST_OFF_NUMBER AS CUST_NUMBER FROM CUSTOMER
)A
ORDER BY ID ASC
SELECT CUST_ID, CUST_NUMBER FROM CUSTOMERS
UNION
SELECT CUST_ID, CUST_OFF_NUMBER FROM CUSTOMERS
WHERE CUST_NUMBER CUST_OFF_NUMBER
;
SELECT CUST_ID, CUST_NUMBER FROM CUSTOMERS
UNION
SELECT CUST_ID, CUST_OFF_NUMBER FROM CUSTOMERS