SSRS Error Maximum Length is 128

Epicor ERP 10
,
1

Adding in a UD table field to our report and get the error Maximum length is 128.

Interestingly, this only happens on fields that were already in this report and working before I started adding in the UD field.

image
image412×520 10.1 KB

2

Do you have the full SQL text for the dataset from the report designer? My guess is there is a missing " mark.

3

"SELECT distinct T1.ShipComment OHShipCmnt, T1.Checkbox13 SpecShipfrom, T1.OrderNum,T1.Calc_BCCustNum,T1.Calc_BCOrderNum,T1.Calc_CustID,T1.Calc_CustName,T1.Calc_HeadComment, T2.DisplaySeq,T2.KitFlag,T2.KitShipComplete,T2.LineDesc,CAST( T2.OrderLine as nvarchar ) as OrderLine,CAST( T2.OrderNum as nvarchar ) as OrderDtl_OrderNum,T2.PartNum,T2.PickListComment,T2.RevisionNum,T2.Calc_BCLineNum,T2.Calc_BCPart,T2.Calc_BCRev,T2.Calc_UOM, T2.Calc_KitPartNum,T3.OrderRelNum,CAST( T3.ReqDate as nvarchar ) as ReqDate,T3.ShipToNum,T3.ShipViaCode,T3.Calc_BCRelNum,T3.Calc_RelQty,T3.Calc_Source,
T5.RptLiteralsLOf,T5.RptLiteralsLBin as BinNum,T5.RptLiteralsLAllocQty,T5.RptLiteralsLComponents,T5.RptLiteralsLDate,T5.RptLiteralsLDimCode,T5.RptLiteralsLJob,T5.RptLiteralsLKit,T5.RptLiteralsLKitSeq,T5.RptLiteralsLLine,T5.RptLiteralsLOnHand,T5.RptLiteralsLPage,T5.RptLiteralsLPickFrm,T5.RptLiteralsLQtyShip,T5.RptLiteralsLRel,T5.RptLiteralsLRelQty,T5.RptLiteralsLShipTo,T5.RptLiteralsLShipVia,T5.RptLiteralsLUOM,T5.RptLiteralsLWhse,T5.RptLiteralsRptTitle,T5.OrderNum as RptLabelsOrderNum, T5.CustNum, T5.PartNum as RptLabelsPartNum, T5.RevisionNum as RptLabelsRevisionNum, T5.RptLiteralsTime , T3.UseOTS, T3.OTSName, T1.UseOTS as OHUseOTS, T1.OTSName as OHOTSName, ST.Name ShipToName, ST.Address1 ShipToAddr1, ST.Address2 ShipToAddr2, ST.Address3 ShipToAddr3, ST.City ShipToCity, ST.State ShipToState, sv.Description ShipViaDesc, ST.Character01 RecHours, T1.PONum, T1.Character08 as OHJob, T1.ShortChar02 OHPaint, T1.ShortChar03 BaseCS, T1.UserChar3 BaseCS1, T1.Character02 WSFinish, T1.Character03 WSTrim, T1.ShortChar04 ShelfPaint, T1.Character04 as PanelFab, T1.Character05 as FlibberFab, T1.Shortchar05 Shelfstyle, T1.Shortchar06 Elect, T1.shortchar07 Rails, T3.OTSContact RelOTSContact, T1.ShortChar08 PunchOrder, T3.OTSPhoneNum RelOTSPhone, UD01.Character01 as Class, T1.Checkbox01 Approved, UD07.Character01 TopCap, UD10.Character01 TakFab,

P.TypeCode,
t2.KitParentLine
, T3.Calc_RelQty CalcRelQty, T2.Calc_OnHandQty Ohq, T2.ShipComment, T3.OTSAddress1, T3.OTSAddress2, T3.OTSAddress3, T3.OTSCity RelOTSCity, T3.OTSState RelOTSState, T3.OTSZip RelOTSZip, T3.OTSAddress1 RelOTSAddr1,
T3.OTSAddress2 RelOTSAddr2, T1.OTSAddress1 OHOTSAddr1, T1.OTSAddress2 OHOTSAddr2, UD08.Character01 PannelFabA, UD09.Character01 FlipperFabA, T1.Character06 TakFabA, T1.OTSCity OHOTSCITY, T1.OTSState OHOTSState,
T1.OTSZip OHOTSZip, ST.Zip ShipToZip, CC.Name CustCntName, T1.OTSContact OHOTSCntName, T3.OTSContact RelOTSCntName, P.UserInteger1 as Puserint, ST.PhoneNum ShiptoPhone, T3.OTSPhonenum ORelOTSPhone, UD04.Character01 WSFinishA,
T1.OTSPhonenum OHOTSPhone, CC.PhoneNum CustCntPhone, NeededMtl, T2.Checkbox02 special, UD16.Key1 OrderNum2, UD16.Key2 ShpLocation
FROM OrderHed_" + Parameters!TableGuid.Value + " T1 with (nolock)

LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2 with (nolock) ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum

LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3 with (nolock) ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine

LEFT OUTER JOIN ShipTo_" + Parameters!TableGuid.Value + " ST with (nolock) on T1.Company = ST.Company and T1.Custnum = ST.Custnum and T1.ShiptoNum = ST.ShipToNum

LEFT OUTER JOIN ShipVia_" + Parameters!TableGuid.Value + " SV with (nolock) on T1.Company = SV.Company and T1.ShipViaCode = SV.ShipViaCode

LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " JP with (nolock) ON T3.Company = JP.Company AND T3.OrderNum = JP.OrderNum and T3.Orderline = JP.Orderline and T3.OrderRelNum = JP.OrderRelNum

LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T5 with (nolock) ON T1.RptLanguageID = T5.RptLanguageID

LEFT OUTER JOIN UD01_" + Parameters!TableGuid.Value + " UD01 with (nolock) on T1.Company = UD01.Company and T1.Shortchar01 = UD01.Key1

LEFT OUTER JOIN UD07_" + Parameters!TableGuid.Value + " UD07 with (nolock) on T1.Company = UD07.Company and T1.Character01 = UD07.Key1

LEFT OUTER JOIN UD10_" + Parameters!TableGuid.Value + " UD10 with (nolock) on T1.Company = UD10.Company and T1.Character06 = UD10.Key1

LEFT OUTER JOIN UD08_" + Parameters!TableGuid.Value + " UD08 with (nolock) on T1.Company = UD08.Company and T1.Character04 = UD08.Key1

LEFT OUTER JOIN UD09_" + Parameters!TableGuid.Value + " UD09 with (nolock) on T1.Company = UD09.Company and T1.Character05 = UD09.Key1

LEFT OUTER JOIN UD04_" + Parameters!TableGuid.Value + " UD04 with (nolock) on T1.Company = UD04.Company and T1.Character02 = UD04.Key1

LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " P with (nolock) on T2.Company = P.Company and T2.Partnum = P.PartNum

LEFT OUTER JOIN UD16_" + Parameters!TableGuid.Value + " UD16 with (nolock) on T1.OrderNum = UD16.Key1

LEFT OUTER JOIN CustCnt_" + Parameters!TableGuid.Value + " CC with (nolock) on T1.Company = CC.Company and T1.CustNum = CC.CustNum and T1.ShipToNum = CC.ShipToNum and T1.ShpConNum= CC.ConNum Left outer join ( select jp.OrderNum,count(jp.ordernum)NeededMtl
FROM JobProd_" + Parameters!TableGuid.Value + " JP left outer join JobMtl_" + Parameters!TableGuid.Value + " JM on JP.Company = JM.Company and JP.Jobnum = JM.Jobnum
where (jp.PartNum not like ‘BCOV%’ or jp.PartNum not like ‘CANT%’) and

(jm.PartNum like ‘BCOV%’ and Right(Left(jm.PartNum,7),1) = ‘T’)
or jm.PartNum like ‘CANT%’
or jm.PartNum like ‘CRNRBR%’
or jm.PartNum like ‘FLATBR%’
or jm.PartNum like ‘GR%’
or jm.PartNum like ‘WSSUP-BRK-U%’

group by JP.OrderNum )NeededMtl on T1.Ordernum = NeededMtl.OrderNum OPTION (RECOMPILE) "

4

I’m not sure if the forum reformatted these. But make sure these are standard single quotes around each of these.

Also, in your subquery you have a column called NeededMtl and you name that subquery NeededMtl. You then reference it in the main query as just NeededMtl. You may want to change some of that to prevent ambiguity.

select jp.OrderNum,count(jp.ordernum) AS RecordCount

CC.PhoneNum CustCntPhone, NeededMtl.RecordCount, T2.Checkbox02 special, UD16.Key1 OrderNum2, UD16.Key2 ShpLocation
1 Like
5

I decided to recopy the report and add my additions in slowly. It now works. Thank you!

1 Like