Table not found error in TSQL component

TomG1 — Sat, 16 Nov 2024 09:28:55 GMT

Hi,

I have created a Bigdata joblet as shown below

I have written a custom query in the Tsql as shown below

"SELECT inEnrichedOrdShp.*,CAST(inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID AS STRING),inBuisnessAreaHierarchy.ValVolumeInd,inBuisnessAreaHierarchy.BusinessGroupCode LkpBusinessGroupCode,
inBuisnessAreaHierarchy.ExternalSegmentCode,inBuisnessAreaHierarchy.GlobalBusinessUnitCode LkpGlobalBusinessUnitCode,inBuisnessAreaHierarchy.FinanceProductCategoryCode,inBuisnessAreaHierarchy1.BusinessAreaCode LkpBusinessAreaCode,
inProductHierarchy.TypeCode,inProductHierarchy.LineCode,inProductHierarchy.FamilyCode
FROM inEnrichedOrdShp LEFT OUTER JOIN
inBuisnessAreaDivisionMapping ON
inEnrichedOrdShp.Product_Line = inBuisnessAreaDivisionMapping.DivisionCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") >= unix_timestamp(inBuisnessAreaDivisionMapping.StartDate,"+"'dd-MM-yy'"+") AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") <= unix_timestamp(inBuisnessAreaDivisionMapping.EndDate,"+"'dd-MM-yy'"+")
LEFT OUTER JOIN inBuisnessAreaHierarchy ON
inEnrichedOrdShp.Business_Area_Code = inBuisnessAreaHierarchy.BusinessAreaCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") >= unix_timestamp(inBuisnessAreaHierarchy.StartDate,"+"'dd-MM-yy'"+") AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") <= unix_timestamp(inBuisnessAreaHierarchy.EndDate,"+"'dd-MM-yy'"+")
LEFT OUTER JOIN inProductHierarchy ON
inEnrichedOrdShp.Product_Part_Number = inProductHierarchy.SKUCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") >= unix_timestamp(inProductHierarchy.StartDate,"+"'dd-MM-yy'"+") AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") <= unix_timestamp(inProductHierarchy.EndDate,"+"'dd-MM-yy'"+")
LEFT OUTER JOIN inBuisnessAreaHierarchy inBuisnessAreaHierarchy1 ON
inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID = inBuisnessAreaHierarchy1.BusinessAreaHierarchyID AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") >= unix_timestamp(inBuisnessAreaHierarchy1.StartDate,"+"'dd-MM-yy'"+") AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,"+"'dd-MM-yy'"+") <= unix_timestamp(inBuisnessAreaHierarchy1.EndDate,"+"'dd-MM-yy'"+")"

I am using this joblet in a big data job as shown below

But when i run the job I am getting table not found error

Parsing command: SELECT inEnrichedOrdShp.*,CAST(inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID AS STRING) BusinessAreaHierarchyID,inBuisnessAreaHierarchy.ValVolumeInd,inBuisnessAreaHierarchy.BusinessGroupCode LkpBusinessGroupCode,
inBuisnessAreaHierarchy.ExternalSegmentCode,inBuisnessAreaHierarchy.GlobalBusinessUnitCode LkpGlobalBusinessUnitCode,inBuisnessAreaHierarchy.FinanceProductCategoryCode,inBuisnessAreaHierarchy1.BusinessAreaCode LkpBusinessAreaCode,
inProductHierarchy.TypeCode,inProductHierarchy.LineCode,inProductHierarchy.FamilyCode
FROM inEnrichedOrdShp LEFT OUTER JOIN
inBuisnessAreaDivisionMapping ON
inEnrichedOrdShp.Product_Line = inBuisnessAreaDivisionMapping.DivisionCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaDivisionMapping.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaDivisionMapping.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inBuisnessAreaHierarchy ON
inEnrichedOrdShp.Business_Area_Code = inBuisnessAreaHierarchy.BusinessAreaCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaHierarchy.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaHierarchy.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inProductHierarchy ON
inEnrichedOrdShp.Product_Part_Number = inProductHierarchy.SKUCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inProductHierarchy.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inProductHierarchy.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inBuisnessAreaHierarchy inBuisnessAreaHierarchy1 ON
inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID = inBuisnessAreaHierarchy1.BusinessAreaHierarchyID AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaHierarchy1.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaHierarchy1.EndDate,'dd-MM-yy')
17/07/26 17:26:40 INFO ParseDriver: Parse Completed
org.apache.spark.sql.AnalysisException: Table not found: inEnrichedOrdShp; line 4 pos 5
	at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)
	at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$.getTable(Analyzer.scala:305)
	at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$$anonfun$apply$9.applyOrElse(Analyzer.scala:314)

At first i Thought this is some spelling mistake .But its not. The link name is correct.

Any idea why this is happening?

PS: I have copied the joblet code into a big data job and it is working fine. when The code is put in the joblet, its is not working.

Re: Table not found error in TSQL component

TomG1 — Fri, 28 Jul 2017 13:36:48 GMT

I have managed to solve the error which I am getting

Give same name to the row link(input) in main job and the row link in the joblet.

Now I am getting a new error. If you see the my joblet mapping it contains lookups. TSQL component is not able to identify the lookup table .

The following is the error

17/07/28 11:33:24 INFO ParseDriver: Parsing command: SELECT inEnrichedOrdShp.*,CAST(inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID AS STRING) BusinessAreaHierarchyID,inBuisnessAreaHierarchy.ValVolumeInd,inBuisnessAreaHierarchy.BusinessGroupCode LkpBusinessGroupCode,
inBuisnessAreaHierarchy.ExternalSegmentCode,inBuisnessAreaHierarchy.GlobalBusinessUnitCode LkpGlobalBusinessUnitCode,inBuisnessAreaHierarchy.FinanceProductCategoryCode,inBuisnessAreaHierarchy1.BusinessAreaCode LkpBusinessAreaCode,
inProductHierarchy.TypeCode,inProductHierarchy.LineCode,inProductHierarchy.FamilyCode
FROM inEnrichedOrdShp LEFT OUTER JOIN
inBuisnessAreaDivisionMapping ON
inEnrichedOrdShp.Product_Line = inBuisnessAreaDivisionMapping.DivisionCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaDivisionMapping.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaDivisionMapping.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inBuisnessAreaHierarchy ON
inEnrichedOrdShp.Business_Area_Code = inBuisnessAreaHierarchy.BusinessAreaCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaHierarchy.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaHierarchy.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inProductHierarchy ON
inEnrichedOrdShp.Product_Part_Number = inProductHierarchy.SKUCode AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inProductHierarchy.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inProductHierarchy.EndDate,'dd-MM-yy')
LEFT OUTER JOIN inBuisnessAreaHierarchy inBuisnessAreaHierarchy1 ON
inBuisnessAreaDivisionMapping.BusinessAreaHierarchyID = inBuisnessAreaHierarchy1.BusinessAreaHierarchyID AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') >= unix_timestamp(inBuisnessAreaHierarchy1.StartDate,'dd-MM-yy') AND
unix_timestamp(inEnrichedOrdShp.Adjusted_Primary_Transaction_Date,'dd-MM-yy') <= unix_timestamp(inBuisnessAreaHierarchy1.EndDate,'dd-MM-yy')
17/07/28 11:33:25 INFO ParseDriver: Parse Completed
org.apache.spark.sql.AnalysisException: Table not found: inBuisnessAreaDivisionMapping; line 5 pos 0
	at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)

topic Table not found error in TSQL component in Talend Studio

Table not found error in TSQL component

Re: Table not found error in TSQL component