SQL Query syntax

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]

Think I figured it out. I needed to move the cast statement line under another join.

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].[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]
and [APInvDtl_UD].[Character01] = CAST(ShipDtl.OrderNum as nvarchar(50))

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]

By the way, if you query dbo.APInvDtl instead of erp.APInvDtl you don’t have to join the UD tables.

Same goes for ShipHead. If you query dbo.ShipHead as [SH] then you can just say Select SH.Checkbox11. No need to join the UD table.

2 Likes

That is good information to know. I guess the dbo is a view that already joins them?

I tried to change but failed miserably. Can you take what I posted an fix so I can see what to do? I am just learning how to build these things.

I think just drop the prefix altogether - just call it APInvDtl yes its a view

2 Likes

Yes