SELECT Failed. 3706: Syntax error: expected something between ')' and the 'as' keywor

Database

SELECT Failed. 3706: Syntax error: expected something between ')' and the 'as' keywor

I'm fairly new to Teradata and SQL in general.  I have written the below code and have gotten the error as shown in the subject of this post.  

Objective: I need a weekly list of sales by department, by area, by week, by channel.

Issue:  If a specific department didn't have a sale for a particular area, channel, or week there is no record of it.  However for my data pull I need there to be a record of this, and simply have a value of zero or NULL.

Process thus far:  I have successfully created a working cross join to create a result table that has every area, every department, every week, and every channel.  Now I need to do a left join to this result set to bring in the sales, and when there is no sale to simply put in a value of NULL.

The individual queries by themselves produce what I want them to, however I can't get the left join to work properly and am hoping you will be able to help.

(select distinct

dim_store.area_code,
dim_merchandise_department.merchandise_department_desc,
dim_sales_channel.sales_channel_identifier,
dim_date.fiscal_year_week

from dim_store, dim_merchandise_department, dim_sales_channel, dim_date

Where

dim_date.fiscal_year_week between 201301 and 201536
and dim_merchandise_department.merchandise_department_desc = 'antifreeze'
and dim_sales_channel.sales_channel_identifier in (1,2,3)

order by dim_date.Fiscal_year_week, area_code, dim_sales_channel.sales_channel_identifier
)as table1

left join(
select distinct
Merchandise_department_desc,
fact_store_product_week_sales.fiscal_year_week,
sum(pos_gross_sales_amount - pos_gross_returns_amount) Net_Sales

from
fact_store_product_week_sales

left join
dim_product on fact_store_product_week_sales.product_sku = dim_product.sku

where
merchandise_department_desc = 'antifreeze'
and fact_store_product_week_sales.fiscal_year_week between 201301 and 201536

group by Merchandise_department_desc, fact_store_product_week_sales.fiscal_year_week
) as table2
on table2.merchandise_department_desc = table1.merchandise_department_desc
and table2.fiscal_year_week = table1.fiscal_year_week

I've been trying to figure this out for sometime and have been using other posts on this forum as a guide, however I'm somewhat stuck and was hoping someone here would be able to assist.

5 REPLIES
Teradata Employee

Re: SELECT Failed. 3706: Syntax error: expected something between ')' and the 'as' keywor

Everything above seems to be the FROM clause of the (missing) main SELECT, e.g.

select table1.area_code, table1.merchandise_department_desc, table1.sales_channel_identifier, table1.fiscal_year_week, table2.Net_Sales from

...

Also note that within the table2 subquery, the join between sales & product will actually be treated as an inner join, because you are requiring a non-NULL product description.

Re: SELECT Failed. 3706: Syntax error: expected something between ')' and the 'as' keywor

thank you so much Fred, it worked.

I was thinking that it would simply bring back the entire result set from the join, I didn't think that I would need a giant outer select statement to pull from the table I was creating.

I'll look into your other note about the join being treated as an inner join.

Thanks again!

Fan

Re: SELECT Failed. 3706: Syntax error: expected something between ')' and the 'as' keywor

I have this select failed 3706 error as well (SELECT Failed 3706: Syntax error: expected something between the 'and' keyword and the 'month' keyword). And it is weird because this code is working on my collegue's laptop, but not working on mine... Can you please help me out? thanks..

SyntaxEditor Code Snippet

select pein, full_name, activation_report_filter, regional_manager, internal_flag, sum(net_activations)FROM SBI_MDU.VMENSA_SALES_COHORT_DLY b
left join GRP_CHANNEL.WG_Field_Hierarchy a 
on b.GM_AGENT_CD = a.pein 
left join grp_channel.rv_salesquotes_and_orders_om c
on b.HOI = c.Order_Ref_No

where b.net_sales_e_OM = '1' AND ACCOUNT_TYPE = 'Residential' 
AND ACCOUNT_TYPE = 'Residential' AND ((SEGMENT IN ('TV-DTH') AND PROVINCE_GROUP IN ('ON', 'QC', 'East', 'West')) OR (SEGMENT IN ('HP', 'Internet', 'TV-FIBE') AND PROVINCE_GROUP IN ('ON', 'QC')))  and month(activation_dt) = month(current_date) and year(activation_dt) = year(current_date) AND activation_dt <= (current_date  - DayNumber_Of_Week(current_date))and regional_manager in ('ALLAN TSE', 'ANDREW COLQUHOUN', 'SYED ALI')group by 1,2,3,4,5

 

Junior Contributor

Re: SELECT Failed. 3706: Syntax error: expected something between ')' and the 'as' keywor

There's no function YEAR or MONTH in Teradata/Standard SQL, those are ODBC functions which might be translated by the ODBC driver into valid SQL (in older versions of ODBC/SQL Assistant based on some settings).

 

Replace with Standard SQL's  EXTRACT(MONTH/YEAR FROM ...)

Fan

Re: SELECT Failed. 3706: Syntax error: expected something between ')' and the 'as' keywor

Thanks so much for your help.