Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 38:19 Invalid column reference 'code__c'
create table analytics_hsf.Test_CHI_Mar28 as
SELECT
CASE
WHEN substring(je.code__c,1,3) = 'MAT' THEN 'MAT'
ELSE substring(je.code__c,2,3)
END AS Item_Group,
CASE
WHEN substring(je.code__c,1,1) = 'C' THEN substring(je.code__c,length(je.code__c)-(Length(je.code__c)-1+1),
length(je.code__c)-1)
ELSE je.code__c
END AS Product_Code,
CASE
WHEN substring(je.product_id__c,1,1) = 'C' THEN
substring(je.product_id__c,Length(je.product_id__c)-(length(je.product_id__c)-1+1),length(je.product_id__c)-1)
ELSE je.product_id__c
END AS Product_ID,
s.supplier_num__c AS Supplier_Num,
Sum(je.units__c) AS Qty,
CASE
WHEN substring(je.code__c, 1, 3) = 'MAT'
AND substring(je.product_id__c,1,4) = 'CBLR' THEN PCO.GWAC__C
ELSE PIO.GWAC__C
END AS GWACC,
CASE
WHEN substring(je.code__c,1,3) = 'MAT'
AND substring(je.product_id__c,1,4) = 'CBLR' THEN PCO.REBATE__C
ELSE PIO.REBATE__C
END AS REBATE,
CASE
WHEN substring(je.code__c,1,3) = 'MAT'
AND substring(je.product_id__c,1,4) = 'CBLR' THEN PCO.NWACC__C
ELSE PIO.NWAC__C
END AS NWACC,
Sum(CASE
WHEN substring(je.code__c, 1, 3) = 'MAT'
AND substring(je.product_id__c, 1, 4) = 'CBLR' THEN
PCO.GWAC__C * je.units__c
ELSE PIO.GWAC__C*je.units__c
END) AS Total_GWACC,
s.name AS Name,
CASE
WHEN substring(je.code__c,1,3) = 'MAT' THEN 'Materials'
ELSE 'Appliances'
END AS Type,
JE.description__c AS Item_Desc,
PIO.component_make__c AS Item_Make
FROM SF_JOB__C AS j
INNER JOIN SF_BIGMACHINES_QUOTE__C AS q
ON j.quote__c = q.id
INNER JOIN SF_OPPORTUNITY AS o
ON q.opportunity__c = o.id
INNER JOIN SF_JOB_ELEMENT__C AS je
ON j.id = je.job__c
INNER JOIN SF_ORDER__C AS ord
ON ord.sap_number__c = je.merchant_order_number__c
INNER JOIN SF_SUPPLIER__C AS s
ON s.id = ord.supplier__c
LEFT JOIN SF_PRODUCT_ORDER__C AS PIO
ON je.product_id__c = PIO.product_code__c
LEFT JOIN SF_PRODUCT_ORDER__C AS PCO
ON je.code__c = PCO.product_code__c
LEFT JOIN SF_QUOTE_PRODUCT__C AS QPC ON Q.id = QPC.bigmachines_quote__c AND je.product_id__c = QPC.part_number__c
WHERE q.is_primary__c = 'true'
AND q.stage__c = 'Quote Finalised - Accepted'
AND j.installation_date__c >= 2018-02-01
AND j.installation_date__c <= 2018-02-28
AND ord.status__c <> 'Cancelled'
AND o.isdeleted = 'false'
AND q.isdeleted = 'false'
AND j.isdeleted = 'false'
AND je.isdeleted = 'false'
AND ord.isdeleted = 'false'
AND s.isdeleted = 'false'
AND j.status__c NOT IN ('Cancelled','Suspended')
AND coalesce(PIO.isdeleted,'false') = 'false'
AND coalesce(PCO.isdeleted,'false') = 'false'
AND QPC.sales_price__c > 0
GROUP BY
CASE
WHEN substring(je.code__c,1,3) = 'MAT' THEN 'MAT'
ELSE substring(je.code__c,2,3)
END,
CASE
WHEN substring(je.code__c,1,1) = 'C' THEN substring(je.code__c,length(je.code__c)-(Length(je.code__c)-1+1),
length(je.code__c)-1)
ELSE je.code__c
END,
CASE
WHEN substring(je.product_id__c,1,1) = 'C' THEN
substring(je.product_id__c,Length(je.product_id__c)-(length(je.product_id__c)-1+1),length(je.product_id__c)-1)
ELSE je.product_id__c
END,
s.supplier_num__c,
CASE
WHEN substring(je.code__c,1,3) = 'MAT'
AND substring(je.product_id__c,1,4) = 'CBLR' THEN PCO.gwac__c
ELSE PIO.gwac__c
END,
CASE
WHEN substring(je.code__c,1,3) = 'MAT'
AND substring(je.product_id__c,1,4) = 'CBLR' THEN PCO.nwac__c
ELSE PIO.nwac__c
END,
CASE
WHEN substring(je.code__c,1,3) = 'MAT'
AND substring(je.product_id__c,1,4) = 'CBLR' THEN
PCO.rebate__c
ELSE PIO.rebate__c
END,
s.name,
CASE
WHEN substring(je.code__c, 1, 3) = 'MAT' THEN 'Materials'
ELSE 'Appliances'
END,
JE.description__c,
PIO.component_make__c;
