JobTraveler…
Trying to link JobHead.JobCode to OrderHed.OrderNum and OrderHed.CustNum to Customer.CustNum. Everything works until I add the field Customer.Name - the System Monitor reports:
Query execution failed for dataset ‘JobHead’. —> System.Data.SqlClient.SqlException: Invalid column name ‘Name’.
Here is the query:
=“SELECT T1.DueDate,T1.IUM,T1.ProdQty,T1.ReqDueDate,T1.StartDate,T1.Calc_MultiJobParts,T1.Calc_OrderQty,T1.Calc_StockQty, T2.AssemblySeq,T2.BomSequence,T2.Company,T2.[Description],T2.DrawNum,T2.IUM as JobAsmbl_IUM,T2.JobNum,T2.OverRunQty,T2.PartNum,T2.PullQty,T2.RequiredQty,T2.RevisionNum,T2.Calc_BCAsmSeq,T2.Calc_BCJobNum,T2.Calc_BCPartNum,T2.Calc_BCRevNum,T2.Calc_comment, T3.CommentText,T3.Instructions,T3.DaysOut,T3.DueDate as JobOper_DueDate,T3.EstProdHours,T3.EstSetHours,T3.Machines,T3.OpCode,T3.OpDesc,T3.OprSeq,T3.PrimaryProdOpDtl,T3.PrimarySetupOpDtl,T3.ProdStandard,T3.RunQty,T3.StartDate as JobOper_StartDate,T3.StdFormat,T3.Calc_BCOpCode,T3.Calc_BCOprSeq,T3.Calc_DispStatus,T3.Calc_OPText,T3.Calc_OPType,T3.Calc_PurPoint,T3.Calc_VendorId,T3.Calc_VendorName,T3.OpCode_OpDesc, T4.CapabilityID,T4.ConcurrentCapacity,T4.DailyProdRate,T4.OpDtlSeq,T4.ProdCrewSize,T4.ResourceGrpID,T4.ResourceID,T4.SetUpCrewSize,T4.SetupOrProd,T4.Calc_BCCapabilityID,T4.Calc_BCResGrpID,T4.Calc_BCResourceID,T4.Calc_CapbltyDesc,T4.Calc_ResDesc,T4.Calc_ResGrpDesc,T4.Calc_SchedResDesc,T4.Calc_SchedResGrpDesc,T4.Calc_SchedResGrpID,T4.Calc_SchedResID, T5.ResourceGrpID as ResourceTimeUsed_ResourceGrpID,T5.ResourceID as ResourceTimeUsed_ResourceID,T5.WhatIf
, T7.OrderNum, T7.CustNum, T8.Name FROM JobHead_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobOpDtl_" + Parameters!TableGuid.Value + " T4
ON T3.Company = T4.Company AND T3.JobNum = T4.JobNum AND T3.AssemblySeq = T4.AssemblySeq AND T3.OprSeq = T4.OprSeq
LEFT OUTER JOIN ResourceTimeUsed_" + Parameters!TableGuid.Value + " T5
ON T4.Company = T5.Company AND T4.JobNum = T5.JobNum AND T4.AssemblySeq = T5.AssemblySeq AND T4.OprSeq = T5.OprSeq AND T4.OpDtlSeq = T5.OpDtlSeq
LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T7
ON T1.Company = T7.Company AND T1.JobCode = T7.OrderNum
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T8
ON T1.Company = T8.Company AND T7.CustNum = T8.CustNum"