Three Separate Queries in one view

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Three Separate Queries in one view

Hi Experts,

 

I have 3 sepearte queries as shown below:

--- TO GET ID'S

 

SEL
ID,
name,
dttm

from

TBL1 
where DTTM =
cast (CURRENT_DATE -30 AS DATE FORMAT 'dd/mm/yyyy')

 

--- TO GET UNIQUE ID'S

SEL
ID,
dttm

from TBL2 

where DTTM =
cast (CURRENT_DATE -30 AS DATE FORMAT 'dd/mm/yyyy')
QUALIFY ROW_NUMBER() OVER
( PARTITION BY Customer_Identifier ORDER BY Customer_Identifier )=1

 

-- TO FIND OUT HOW MANY ID'S CONTACTED HOW MANY TIMES

SEL
Y.Id,
Y.NoOfEmails

from
(
SEL x.Id ,
Max(x.Cntr) AS NoOfEmails

from
(
SEL
Id,
name,
Dttm,
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id ) as Cntr


from TB3 
INNER JOIN TB2 ON ( TB3.ID=TB2.ID)

) x group by 1

) Y GROUP BY 2;


Now my requirement is to populate the ID's ( from first table) , unique id's ( from 2nd table)
and id's and noofemails ( from 3rd table) in a single view.

PS: the queries seperately will give the required o/p

Is it possible to implement this with id as the join column?
If so please let me know how.

 

Thanks in Advance
Swapnanil

1 REPLY 1
Highlighted
Teradata Employee

Re: Three Separate Queries in one view

Hi Swapnanil,

 

You can go for an aggregate solution like this :

with cte_union_all (id, name, dttm1, dttm2, NoOfEmails) as
(
select id, name, ddtm, null, null from cte_first_qry
 union all
select id, null, null, dttm, null from cte_second_qry
 union all
select id, null, null, null, NoOfEmails from cte_third_qry
)
  ,  cte_first_qry  as (select id, name, dttm...)
  ,  cte_second_qry as (select id, dttm...)
  ,  cte_third_qry  as (select id, NoOfEmails...)
  select id, max(name), max(dttm1), max(dttm2), max(NoOfEmails)
    from cte_union_all
group by id