Okay… Here is goes:
Currently getting this error:
Incorrect syntax near the keyword 'as'.
My thought is that it could be in the calculated fields I’m using in the CTE/Recursion. I’m not sure if the fields I use in the calculations need table prefixes. I did NOT use one in the first select statement but did in the second.
Also, my “Calculated_AssyLevel” field is just set to a constant value of (1)… do I call that out in my select statements?
I don’t want to alter the base “as” aliases on other fields because the RDL may be using them in all sorts of fields throughout the report (if statements, etc.). Don’t want to get the CTE working and have everything else (including subreports) break.
Query:
=";WITH JACTE
(
Description,
PartNum as JobAsmbl_PartNum,
RevisionNum as JobAsmbl_RevisionNum,
TLABurdenCost,
TLALaborCost,
TLAMaterialBurCost,
TLAMaterialCost,
TLAMaterialLabCost,
TLAMaterialMtlCost,
TLAMaterialSubCost,
TLAMtlBurCost,
TLAProdHours,
TLASetupHours,
TLASubcontractCost,
TLEBurdenCost,
TLELaborCost,
TLEMaterialCost,
TLEMtlBurCost,
TLEProdHours,
TLESetupHours,
TLESubcontractCost,
Calc_SerialNoList,
Calc_ProfitInvcAmt,
Calc_AttributeSetShortDescription,
Company,
JobNum as JobAsmbl_JobNum,
AssemblySeq,
Parent,
Calculated_AssySeqPath,
Calculated_AssyLevel
)
AS
(
SELECT
T2.Description,
T2.PartNum as JobAsmbl_PartNum,
T2.RevisionNum as JobAsmbl_RevisionNum,
T2.TLABurdenCost,
T2.TLALaborCost,
T2.TLAMaterialBurCost,
T2.TLAMaterialCost,
T2.TLAMaterialLabCost,
T2.TLAMaterialMtlCost,
T2.TLAMaterialSubCost,
T2.TLAMtlBurCost,
T2.TLAProdHours,
T2.TLASetupHours,
T2.TLASubcontractCost,
T2.TLEBurdenCost,
T2.TLELaborCost,
T2.TLEMaterialCost,
T2.TLEMtlBurCost,
T2.TLEProdHours,
T2.TLESetupHours,
T2.TLESubcontractCost,
T2.Calc_SerialNoList,
T2.Calc_ProfitInvcAmt,
T2.Calc_AttributeSetShortDescription,
T2.Company as JobAsmbl_Company,
T2.JobNum as JobAsmbl_JobNum,
T2.AssemblySeq as JobAsmbl_AssemblySeq,
T2.Parent as JobAsmbl_Parent,
(cast(FORMAT(T2.AssemblySeq,'000') as nvarchar(1000))) as Calculated_AssySeqPath,
(1) as Calculated_AssyLevel
FROM JobAsmbl_" + Parameters!TableGuid.Value + " T2
WHERE T2.AssemblySeq = 0
UNION ALL
SELECT
T22.Company as JobAsmbl1_Company,
T22.JobNum as JobAsmbl1_JobNum,
T22.AssemblySeq as JobAsmbl1_AssemblySeq,
T22.Parent as JobAsmbl1_Parent,
(CAST(T2.Calculated_AssySeqPath + '.' + FORMAT(T22.AssemblySeq, '000') as nvarchar(1000))) as Calculated_AssySeqPath2,
(T2.Calculated_AssyLevel+1) as Calculated_AssyLevel2
FROM JobAsmbl_" + Parameters!TableGuid.Value + " T22
INNER JOIN T2 ON T22.Company = T2.Company AND T22.JobNum = T2.JobNum AND T22.Parent = T2.AssemblySeq
WHERE T22.AssemblySeq <> 0
)
SELECT
T1.ClosedDate,
T1.IUM,
T1.JobCompletionDate,
T1.JobNum,
T1.PartNum,
T1.ProdCode,
T1.ProdQty,
T1.QtyCompleted,
T1.ReqDueDate,
T1.RevisionNum,
T1.Calc_JobStatus,
T1.Calc_MakeToJobQty,
T1.Calc_MakeToOrderQty,
T1.Calc_MakeToStockQty,
T1.Calc_RecToJobQty,
T1.Calc_RecToStockQty,
T1.Calc_ShippedOrderQty,
T1.ProdCode_Description,
T3.ActBurCost,
T3.ActLabCost,
T3.ActProdHours,
T3.ActProdRwkHours,
T3.ActSetupHours,
T3.EstProdHours,
T3.EstSetHours,
T3.OpCode,
T3.OprSeq,
T3.PrimaryProdOpDtl,
T3.PrimarySetupOpDtl,
T3.ProdStandard,
T3.QtyCompleted as JobOper_QtyCompleted,
T3.RunQty,
T3.SetupPctComplete,
T3.StdFormat,
T3.SetUpCrewSize,
T3.Calc_ActRwkHrs,
T3.Calc_AttainedStd,
T3.Calc_EffPct,
T3.Calc_RptProdCrewSize,
T3.Calc_RptSetUpCrewSize,
T3.OpDesc,
T4.CapabilityID,
T4.DailyProdRate,
T4.OpDtlSeq,
T4.OprSeq as JobOpDtl_OprSeq,
T4.ResourceGrpID,
T4.ResourceID,
T4.ResourceGrpID_DailyProdQty,
T4.ResourceGrpID_Description,
T4.CapabilityID_Description,
T4.ResourceID_Description,
T4.ResourceID_DailyProdQty
FROM JobHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JACTE ON T1.Company = JACTE.Company AND T1.JobNum = JACTE.JobNum
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3 ON JACTE.Company = T3.Company AND JACTE.JobNum = T3.JobNum AND JACTE.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"
