Working on building and SSRS Report but have one line in the query not happy. Any suggestions on how to resolve the multipart identifier issue. It is highlighted below. the field is [APInvDtl_UD].[Character01] right next to the Cast statement.
select
[ShipDtl].[OrderNum] as [ShipDtl_OrderNum],
[ShipDtl].[OrderLine] as [ShipDtl_OrderLine],
[ShipDtl].[PartNum] as [ShipDtl_PartNum],
[ShipDtl].[PackNum] as [ShipDtl_PackNum],
[ShipDtl].[OurInventoryShipQty] as [ShipDtl_OurInventoryShipQty],
[ShipHead].[TrackingNumber] as [ShipHead_TrackingNumber],
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
[OrderHed].[ShipViaCode] as [OrderHed_ShipViaCode],
[Customer].[Name] as [Customer_Name],
[Customer].[City] as [Customer_City],
[Customer].[State] as [Customer_State],
[Customer].[Address1] as [Customer_Address1],
[Customer].[Zip] as [Customer_Zip],
[ShipDtl].[ShipToNum] as [ShipDtl_ShipToNum],
[ShipDtl].[DiscountPercent] as [ShipDtl_DiscountPercent],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
[OrderDtl].[ProdCode] as [OrderDtl_ProdCode],
[OrderDtl].[DiscountPercent] as [OrderDtl_DiscountPercent],
case
when [ShipHead_UD].[CheckBox11] = 1 then [ShipHead_UD].[ShortChar02]
else [ShipHead].[TrackingNumber]
end
as [Calculated_TrackNum],
[InvcDtl].[OurShipQty] as [InvcDtl_OurShipQty],
[InvcDtl].[DocUnitPrice] as [InvcDtl_DocUnitPrice],
case
when [APInvHed].[InvoiceDate] > convert(date, ‘20200817’, 112) then [APInvDtl].[UnitCost] * 1.03
else [APInvDtl].[UnitCost]
end
as [Calculated_UnitCost],
case
when [APInvHed].[InvoiceDate] > convert(date, ‘20200817’, 112) then [APInvDtl].[UnitCost] * 1.03
else [APInvDtl].[UnitCost]
end
as [Calculated_DocUnitCost],
[APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],
[APInvDtl].[VendorQty] as [APInvDtl_VendorQty],
[APInvDtl].[OurQty] as [APInvDtl_OurQty],
(((select
(Sum(InvcMisc.MiscAmt)) as [Calculated_Misc]
from Erp.InvcMisc as InvcMisc
where (not [InvcMisc].[MiscCode] in (‘frt1’, ‘frt2’, ‘frt3’, ‘frt4’))
and ([InvcMisc].[InvoiceNum] = [InvcDtl].[InvoiceNum] and [InvcMisc].[InvoiceLine] = [InvcDtl].[InvoiceLine])))) as [Calculated_MA]
from Erp.ShipHead as ShipHead
inner join Erp.ShipDtl as ShipDtl on
[ShipDtl].[Company] = [ShipHead].[Company]
and [ShipDtl].[PackNum] = [ShipHead].[PackNum]
and [ShipDtl].[Company] = ‘GNC’
inner join Erp.OrderHed as OrderHed on
[ShipDtl].[Company] = [OrderHed].[Company]
and [ShipDtl].[OrderNum] = [OrderHed].[OrderNum]
and [OrderHed].[Company] = ‘GNC’
inner join Erp.Customer as Customer on
[OrderHed].[Company] = [Customer].[Company]
and [OrderHed].[CustNum] = [Customer].[CustNum]
and [Customer].[Company] = ‘GNC’
left outer join Erp.APInvDtl as APInvDtl on
[APInvDtl_UD].[Character01] = CAST(ShipDtl.OrderNum as nvarchar(50))
and [APInvDtl].[Company] = [ShipDtl].[Company]
and [APInvDtl].[PartNum] = [ShipDtl].[PartNum]
and [APInvDtl].[Company] = ‘GNC’
and [APInvDtl].[VendorQty] > 0
left outer join Erp.APInvHed as APInvHed on
[APInvDtl].[Company] = [APInvHed].[Company]
and [APInvDtl].[VendorNum] = [APInvHed].[VendorNum]
and [APInvDtl].[InvoiceNum] = [APInvHed].[InvoiceNum]
and [APInvHed].[Company] = ‘GNC’
left outer join Erp.APInvDtl_UD as APInvDtl_UD on
[APInvDtl].SysRowID = [APInvDtl_UD].[ForeignSysRowID]
left outer join Erp.ShipHead_UD as ShipHead_UD on
[ShipHead].SysRowID = [ShipHead_UD].[ForeignSysRowID]
inner join Erp.OrderDtl as OrderDtl on
[ShipDtl].[Company] = [OrderDtl].[Company]
and [ShipDtl].[OrderNum] = [OrderDtl].[OrderNum]
and [ShipDtl].[OrderLine] = [OrderDtl].[OrderLine]
and [OrderDtl].[Company] = ‘GNC’
inner join Erp.Part as Part on
[ShipDtl].[Company] = [Part].[Company]
and [ShipDtl].[PartNum] = [Part].[PartNum]
and [Part].[Company] = ‘GNC’
left outer join Erp.InvcDtl as InvcDtl on
[ShipDtl].[Company] = [InvcDtl].[Company]
and [ShipDtl].[PackNum] = [InvcDtl].[PackNum]
and [ShipDtl].[PackLine] = [InvcDtl].[PackLine]
where [ShipHead].[Company] = ‘GNC’ and not [ShipHead].[ShipViaCode] = ‘CT1’ and [ShipHead].[ShipDate] > DATEADD(month, -1, GETDATE())
and not [Part].[ProdCode] in (‘CENTURY’, ‘concret’, ‘GALCUL’, ‘HDPEC’, ‘TP-NDB’)
order by [ShipDtl].[OrderLine]