Hadoop Issue

[saikiran_nemani]

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;

[SimonDawson]

saikiran_nemani please try distil the issue you are facing down a little bit more.

For problems being experience with code please try spend some time to strip the program down to the smallest piece of code that shows the issue.

Include a little context if needed, explain a little bit about the issue you are experiencing and briefly outline what help you need.

Placing a wall of SQL without any context into a new thread typically won't get you the best responses.

[Kurt_Bremser]

Look at the columns in SF_JOB_ELEMENT__C.

Code formatting is preserved when the advice for posting code in https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce is heeded.

[saikiran_nemani]

I am still not getting the output . Can you Resolve the problem?