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.
from dim_store, dim_merchandise_department, dim_sales_channel, dim_date
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
sum(pos_gross_sales_amount - pos_gross_returns_amount) Net_Sales
dim_product on fact_store_product_week_sales.product_sku = dim_product.sku
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.
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.
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.
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
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 ...)