Teradata OLAP functions in SQL

Analytics
N/A

Teradata OLAP functions in SQL

Hello all,

I need your advise on writing the following query in a more efficient way using OLAP functions like sum() over partition by. Instead of using 2 queries, can I use just one query to accomplish the same results?

btw, query p returns 2 rows and query q has 10 rows but the final result should render only 2 rows (with the common prd_id from the 1st query).

Please advise. 

Thanks much,

Sue.

select x.prd_id,

 x.prd_name,

 x.p_amt,

 x.p_cnt,

 x.px_amt,

 x.px_cnt,

 x.ap_amt,

 x.ap_cnt,

 x.apx_amt,

 x.apx_cnt

from

(

(

select  a.prd_id,

  a.prd_name,

 sum(case when (a.prd_cd='X' and a.prd_name like b.prdname) then a.amt else 0 end) as  p_amt,

 sum(case when (a.prd_cd='X' and a.prd_name like b.prdname) then 1 else 0 end) as  p_cnt,

 sum(case when (a.prd_cd='Y' and a.prd_name like b.prdname) then a.amt else 0 end) as  px_amt,

 sum(case when (a.prd_cd='Y' and a.prd_name like b.prdname) then 1 else 0 end) as  px_cnt,

from t_prod a,

 (select prdname from prdlkup) b

group by 1,2

) p

inner join

(

select  a.prd_id,

  a.prd_name,

 sum(case when a.prd_cd='X'  then a.amt else 0 end) as  ap_amt,

  sum(case when a.prd_cd='X'  then 1 else 0 end) as  ap_cnt,

  sum(case when a.prd_cd='Y' then a.amt else 0 end) as  apx_amt,

  sum(case when a.prd_cd='Y'  then 1 else 0 end) as  apx_cnt,

from t_prod a

group by 1,2

on

) q

where p.prd_id = q.prd_id

) x

5 REPLIES
N/A

Re: Teradata OLAP functions in SQL

Hi Sue,

the p query is strange, do you really need a cross join between a and b?

Is b.prdname actually including wildcards?

Can you show some input/expected result set?

N/A

Re: Teradata OLAP functions in SQL

Hi dnoeth,

Thanks so much for your response!! Yes, b.prdname includes wildcards like '%abc%', '%pqr%', '%aabc%', '%xyz%'. This lookup table is a single column table that has about 90 string patterns. That is the reason why I had to use a cross join.

The first query p should render aggregates based on prd_cd and matching string patterns(there are multiple prd_names for prd_id). The result for this query gives 2 records with aggregates where the prd_name matches the string patterns.

The second query q renders say 10 records with all the aggregates for all the prd_names by prd_id irrespective of the string patterns. The final query should have 2 records from the p query and the matching prd_id with its aggregates from q query. 

This query works fine but I would like know if we have a much better way of writing sql in teradata using olap functions like sum() over partition by  or count() over partition by etc., meaning instead of writing 2 subqueries can I accomplish the same with one query.

Please advise.

As always thanks so much for all your help!

Sue

N/A

Re: Teradata OLAP functions in SQL

Hi Dieter,

Can you please advise if there is a better way to write the query above without using two derived tables?

Thanks for your help!!

Sue

N/A

Re: Teradata OLAP functions in SQL

Hi Sue,

I'm not sure, but you might do the 2nd sum in a Derived Table and then cross join to prdlkup and use a SUM OVER like this, fully untested :-)

select  a.prd_id,
a.prd_name,
ap_amt,
ap_cnt,
apx_amt,
apx_cnt,
sum(case when (a.prd_cd='X' and a.prd_name like b.prdname) then a.amt else 0 end) over () as p_amt,
sum(case when (a.prd_cd='X' and a.prd_name like b.prdname) then 1 else 0 end) over () as p_cnt,
sum(case when (a.prd_cd='Y' and a.prd_name like b.prdname) then a.amt else 0 end) over () as px_amt,
sum(case when (a.prd_cd='Y' and a.prd_name like b.prdname) then 1 else 0 end) over () as px_cnt,
from
(
select a.prd_id,
a.prd_name,
sum(case when a.prd_cd='X' then a.amt else 0 end) as ap_amt,
sum(case when a.prd_cd='X' then 1 else 0 end) as ap_cnt,
sum(case when a.prd_cd='Y' then a.amt else 0 end) as apx_amt,
sum(case when a.prd_cd='Y' then 1 else 0 end) as apx_cnt,
from t_prod a
group by 1,2
) as a,
(select prdname from prdlkup) b
N/A

Re: Teradata OLAP functions in SQL

Hi Dieter,

Thanks so much for your help and time!! You're the best! :)

Thanks

Sue