Joining tables issue

Database
Enthusiast

Joining tables issue

I have a table  with data  68710147 and  i'm using the same table  in the joing part to get the counts and the sum's

like :

Sel

prd.product_name,

,count(prd1.product_id)

,sum(prd1.product_amt)  product_name_amnt

from  table_1 prd

left join table_1  prd1 on prd.prod_id=prd1.prod and prd.dt=prd1.dt

left join table_1  prd2   on prd.prod_id=prd2.prod and prd.dt=prd2.dt

this type of query is consuming too much of CPU space and utilization.

I'm  unable to proceed further to execute this query.  I'm using Teradata 13.10 version.

any one please suggest me its  urgent.

6 REPLIES
Senior Apprentice

Re: Joining tables issue

68710147? Number of rows?

You don't need the second outer join, as you don't use any column from it.

And do the aggregation before the join:

Sel
prd.product_name,
prd1.product_count,
prd1,product_name_amnt
from table_1 prd
left join
(select
product_id
,count(*) product_count
,sum(product_amt) product_name_amnt
from table_1
) prd1
on prd.prod_id=prd1.prod and prd.dt=prd1.dt

Dieter

Enthusiast

Re: Joining tables issue

I have a table  with data records count  68710147  and  i'm using the same table  in the joing part to get the counts and the sum's

like :

Sel

prd.product_name,

,count(prd1.product_id)

,sum(prd1.product_amt)  product_name_amnt

from  table_1 prd

left join table_1  prd1 on prd.prod_id=prd1.prod and prd.dt=prd1.dt

left join table_1  prd2   on prd.prod_id=prd2.prod and prd.dt=prd2.dt

this type of query is consuming too much of CPU space and utilization.

I'm  unable to proceed further to execute this query.  I'm using Teradata 13.10 version.

any one please suggest me its  urgent.

i have 7 left joines with the same table.

Enthusiast

Re: Joining tables issue

@dnoeth   : 

Thanks for your reply.

here  i have different products and i dont know in advance  how many products.

So we are using procedure to build the sql query dynamically.

by using the procedure the Sql statement is created with 7 left joins means 7 different products used by

N number of customers.

how to use this query to repalce 7 joins

Enthusiast

Re: Joining tables issue

the query will be like this from my stored procedure


@dnoeth   : 

Sel

prd.cust,

prd.product_name,

sum(prd1.product_amt)  product_a_amnt

,sum(prd2.product_amt)  product_a_amnt

from  table_1 prd

left join table_1  prd1 on prd.prod_id=prd1.prod and prd.dt=prd1.dt

left join table_1  prd2   on prd.prod_id=prd2.prod and prd.dt=prd2.dt

group by 1,2

I don't know how many left joins will be there in the Sql statement,right now i have 7 joins. In near future it will be more than that.

Senior Apprentice

Re: Joining tables issue

What are you trying to get as a result? I can't follow.

When you want some specific products, why there's no WHERE condition?

This looks like a kind of pivot query, this is usually done with some CASEs:

sum(case when ??? then prd1.product_amt else 0 end)  product_a_amnt

Dieter

Enthusiast

Re: Joining tables issue

yes this is a pivot query by using stored procedure.

i want a resultant dataset like this

cust_id  prod_id prod_name  prod_a_amnt   prod_b_amnt  ................

123         10           TD678         1800 Euros     

I dont know how many prod_amount are there for a customer, it  need to dynamically add a column.

also i dont know the how many number of products will be there in the table.

each a new product record is inserted into the table, it should appear as a column.