Teradata Syntax Error

Database

Teradata Syntax Error

select  
all_members.member_amisys_nbr,
eff_date.date_date as effective_date,
end_date.date_date as end_date

from etl_access_own.dim_date as eff_date

inner join on
etl_access_own.dim_member_eligibility as member_eligibility
(eff_date.date_dim_ck=member_eligibility.eligibility_end_date_dim_ck)

inner join on
etl_access_own.dim_date as end_date
(end_date.date_dim_ck=member_eligibility.eligibility_end_date_dim_ck)

inner join on
etl_access_own.dim_member as all_members
(member_eligibility.edw_member_ck=all_members.edw_member_ck)

where ((member_eligibility.plan.dim_ck in (14)) and
(all_members.plan_dim_ck in (14)) and
all_members.active_ind = 'Y' and
member_eligibility.version_active_ind ='y' and
member_eligibility.deleted_ind ='n')
2 REPLIES

Re: Teradata Syntax Error

SELECT Failed. 3707:  Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'join' keyword and the 'on' keyword. 

get the above syntax error after executing script: 

Junior Contributor

Re: Teradata Syntax Error

Your syntax is not valid, you need to move the ON before the join-condition:

select 
all_members.member_amisys_nbr,
eff_date.date_date as effective_date,
end_date.date_date as end_date

from etl_access_own.dim_date as eff_date

inner join etl_access_own.dim_member_eligibility as member_eligibility
on (eff_date.date_dim_ck=member_eligibility.eligibility_end_date_dim_ck)

inner join etl_access_own.dim_date as end_date
(end_date.date_dim_ck=member_eligibility.eligibility_end_date_dim_ck)

inner join etl_access_own.dim_member as all_members
on (member_eligibility.edw_member_ck=all_members.edw_member_ck)

where ((member_eligibility.plan.dim_ck in (14)) and
(all_members.plan_dim_ck in (14)) and
all_members.active_ind = 'Y' and
member_eligibility.version_active_ind ='y' and
member_eligibility.deleted_ind ='n')