"Teradata Unable to Perform Join"

Database
Enthusiast

"Teradata Unable to Perform Join"

I have the following join indexed defined:
create join index session_base_ji
as
select wh.visid_high
, wh.visid_low
, wh.visit_num
, wh.prop49 as user_id
, wh.hit_dt
, wh.omniture_dtm
, pco.client_organization_id
, tpd.time_period_description
, tpd.calendar_number
, wh.rowid as wh_rowid
from web_hits wh inner join
person_client_org pco on wh.prop49 = pco.web_rowguid inner join
time_period_detail tpd on wh.hit_dt = tpd.calendar_dt
;

I have the following view defined:
replace view vwsession_base
as
select wh.visid_high
, wh.visid_low
, wh.visit_num
, wh.prop49 as user_id
, wh.hit_dt
, wh.omniture_dtm
, pco.client_organization_id
, tpd.time_period_description
, tpd.calendar_number
from web_hits wh
inner join person_client_org pco
on wh.prop49 = pco.web_rowguid
inner join time_period_detail tpd
on wh.hit_dt = tpd.calendar_dt
;

Explain select * from vwsession_base includes:
3) We do an all-AMPs RETRIEVE step from TEST.SESSION_BASE_JI by way of an all-rows scan with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs.
...

The following query results in a message that reads:
"The request uses a join condition that Teradata is unable to perform."

select Visid_High
, Visid_Low
, Visit_Num
, user_id
, min(hit_dt) as session_dt
, min(omniture_dtm) as session_start_dtm
, max(omniture_dtm) as session_end_dtm
, session_end_dtm - session_start_dtm minute(4) to second as session_length_min
, count(*) as page_view_cnt
, client_organization_id
, time_period_description
, calendar_number
From vwsession_base
group by visid_high
, visid_low
, visit_num
, user_id
, client_organization_id
, time_period_description
, calendar_number
;

The problem seems to be with max(omniture_dtm) and min(Omniture_dtm). Omniture_dtm is a timestamp.

Can anyone explain what this means, and how I can fix it?
Also, I get the same problem if I write the query against the tables directly (and don't use the view).

Thanks

James
1 REPLY
Enthusiast

Re: "Teradata Unable to Perform Join"

I found the problem. I can't have an aggregation on a column that I am joining on.