Unable to retrieve ALL records from Top BAQ due to Subquery

Kinetic ERP
1

I need a report that shows our order backlog at the release level. One of the fields required is the total shipped quantity for the release. I created a subquery that sums the total ship quantity by order release:

Sum(ShipDtl.OurInventoryShipQty)+ Sum(ShipDtl.OurJobShipQty)

When I pull the subquery into my top-level and connect to the OrderRel table (by OrderNum, OrderLine, OrderRel) the shipping quantity is working but only the order releases with a shipped quantity are showing. I want all order releases to show whether or not there have been any shipments.

When I change the join type from Matching Rows to All rows from OrderRel, the BAQ breaks. I have been looking at this too long and my brain is mush. I am hoping it is a quick and easy solution one of you smart people can help me with.

select 
	[OrderDtl].[SalesCatID] as [OrderDtl_SalesCatID],
	[OrderHed].[SalesForceNum_c] as [OrderHed_SalesForceNum_c],
	[OrderHed].[CustNum] as [OrderHed_CustNum],
	[Customer].[Name] as [Customer_Name],
	[OrderHed].[BTCustNum] as [OrderHed_BTCustNum],
	[Customer].[BTName] as [Customer_BTName],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[PartNum] as [OrderRel_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[OrderRel].[RevisionNum] as [OrderRel_RevisionNum],
	[ProdGrup].[ProdCode] as [ProdGrup_ProdCode],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[OrderHed].[RequestDate] as [OrderHed_RequestDate],
	((case when OrderDtl.SalesCatID='CPR' then OrderHed.RequestDate  else OrderDtl.RequestDate end)) as [Calculated_Ship_By_combined],
	[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
	((OrderRel.SellingReqQty - OrderRel.SellingJobShippedQty - OrderRel.SellingStockShippedQty)) as [Calculated_OpenQty],
	[OrderDtl].[ProjectID] as [OrderDtl_ProjectID],
	[OrderHed].[OrderMngr_c] as [OrderHed_OrderMngr_c],
	[EmpBasic].[Name] as [EmpBasic_Name],
	[ShipVia].[Description] as [ShipVia_Description],
	[OrderHed].[PONum] as [OrderHed_PONum],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[OrderRel].[OpenRelease] as [OrderRel_OpenRelease],
	[Customer].[CustID] as [Customer_CustID],
	[Customer].[Company] as [Customer_Company],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	[OrderRel].[ShipViaCode] as [OrderRel_ShipViaCode],
	[OrderDtl].[SalesUM] as [OrderDtl_SalesUM],
	[OrderDtl].[SellingQuantity] as [OrderDtl_SellingQuantity],
	[JobProd].[JobNum] as [JobProd_JobNum],
	[OrderHed].[FOB] as [OrderHed_FOB],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	[SubQuery2].[Calculated_UnitsShipped] as [Calculated_UnitsShipped01],
	(OrderRel.SellingReqQty-SubQuery2.Calculated_UnitsShipped) as [Calculated_UnitsOpen],
	[OrderDtl].[DocUnitPrice] as [OrderDtl_DocUnitPrice],
	((OrderDtl.DocDiscount/OrderDtl.SellingQuantity)) as [Calculated_UnitDisUSD],
	(OrderDtl.DocUnitPrice-UnitDisUSD) as [Calculated_UnitNetUSD],
	(OrderRel.OurReqQty*OrderDtl.DocUnitPrice) as [Calculated_ExtGrossUSD],
	(OrderRel.OurReqQty*UnitDisUSD) as [Calculated_ExtDiscUSD],
	(ExtGrossUSD-ExtDiscUSD) as [Calculated_ExtNetUSD],
	(UnitsOpen*UnitNetUSD) as [Calculated_ExtNetOpenUSD],
	(OrderDtl.DocUnitPrice/OrderHed.ExchangeRate) as [Calculated_UnitGrossCAD],
	(UnitDisUSD/OrderHed.ExchangeRate) as [Calculated_UnitDisCAD],
	(UnitNetUSD/OrderHed.ExchangeRate) as [Calculated_UnitNetCAD],
	(ExtGrossUSD/OrderHed.ExchangeRate) as [Calculated_ExtGrossCAD],
	(ExtDiscUSD/OrderHed.ExchangeRate) as [Calculated_ExtDiscCAD],
	(ExtNetUSD/OrderHed.ExchangeRate) as [Calculated_ExtNetCAD],
	(ExtNetOpenUSD/OrderHed.ExchangeRate) as [Calculated_ExtNetOpenCAD]
from Erp.OrderHed as OrderHed
right outer join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
left outer join Erp.ShipVia as ShipVia on 
	OrderRel.Company = ShipVia.Company
	and OrderRel.ShipViaCode = ShipVia.ShipViaCode
left outer join Erp.ShipDtl as ShipDtl1 on 
	OrderRel.Company = ShipDtl1.Company
	and OrderRel.OrderNum = ShipDtl1.OrderNum
	and OrderRel.OrderLine = ShipDtl1.OrderLine
	and OrderRel.OrderRelNum = ShipDtl1.OrderRelNum
inner join  (select 
	[OrderDtl1].[OrderLine] as [OrderDtl1_OrderLine],
	[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum],
	[ShipDtl].[OrderRelNum] as [ShipDtl_OrderRelNum],
	(Sum(ShipDtl.OurInventoryShipQty)+ Sum(ShipDtl.OurJobShipQty)) as [Calculated_UnitsShipped],
	[ShipDtl].[ReadyToInvoice] as [ShipDtl_ReadyToInvoice]
from Erp.ShipDtl as ShipDtl
inner join Erp.OrderDtl as OrderDtl1 on 
	ShipDtl.Company = OrderDtl1.Company
	and ShipDtl.OrderNum = OrderDtl1.OrderNum
	and ShipDtl.OrderLine = OrderDtl1.OrderLine
where (ShipDtl.ReadyToInvoice = true)
group by [OrderDtl1].[OrderLine],
	[OrderDtl1].[OrderNum],
	[ShipDtl].[OrderRelNum],
	[ShipDtl].[ReadyToInvoice])  as SubQuery2 on 
	ShipDtl1.OrderNum = SubQuery2.OrderDtl1_OrderNum
	and ShipDtl1.OrderLine = SubQuery2.OrderDtl1_OrderLine
	and ShipDtl1.OrderRelNum = SubQuery2.ShipDtl_OrderRelNum
left outer join Erp.ProdGrup as ProdGrup on 
	OrderDtl.Company = ProdGrup.Company
	and OrderDtl.ProdCode = ProdGrup.ProdCode
left outer join Erp.JobProd as JobProd on 
	OrderDtl.Company = JobProd.Company
	and OrderDtl.OrderNum = JobProd.OrderNum
	and OrderDtl.OrderLine = JobProd.OrderLine
left outer join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum
left outer join Erp.EmpBasic as EmpBasic on 
	EmpBasic.EmpID = OrderHed.OrderMngr_c
where (OrderHed.OpenOrder = True)
order by OrderHed.Company, OrderHed.OrderNum, OrderRel.OrderLine, OrderRel.OrderRelNum
2

You need to left outer join to your subquery then.

inner join (select
[OrderDtl1].[OrderLine] as [OrderDtl1_OrderLine],
[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum],
[ShipDtl].[OrderRelNum] as [ShipDtl_OrderRelNum],
(Sum(ShipDtl.OurInventoryShipQty)+ Sum(ShipDtl.OurJobShipQty)) as [Calculated_UnitsShipped],
[ShipDtl].[ReadyToInvoice] as [ShipDtl_ReadyToInvoice]
from Erp.ShipDtl as ShipDtl
inner join Erp.OrderDtl as OrderDtl1 on
ShipDtl.Company = OrderDtl1.Company
and ShipDtl.OrderNum = OrderDtl1.OrderNum
and ShipDtl.OrderLine = OrderDtl1.OrderLine
where (ShipDtl.ReadyToInvoice = true)
group by [OrderDtl1].[OrderLine],
[OrderDtl1].[OrderNum],
[ShipDtl].[OrderRelNum],
[ShipDtl].[ReadyToInvoice]) as SubQuery2

3

When I change it to a left outer join, the query returns no rows.

Severity: Error, Table: , Field: , RowID: , Text: Bad SQL statement. Review the server event logs for details…

image
image1457Ă—780 39 KB

4

what does event viewer on the server show for this error?

5

I am on cloud and will need to create an EpicCare ticket to get that. I’ll respond once I have it. Thanks!

6

You want to the join to return all rows from the OrderRel table

If you return all rows from the subquery, then the subquery acts as a filter eliminating records from the top level query for which it doesn’t have any results.

Returning all rows from both is only used when combining results from two different queries and will play havoc with what you’re wanting to do.

Just a bit of advice, the OrderRel table records the summed OurInventoryShipped & OurJobShipped quantities as well whenever a shipment is made. It might serve you better to eliminate the subquery altogether.

There’s only a few cases where I’ve encountered there being a mismatch on the OrderRel table, and that’s when someone uses our updatable dashboard for updating a UD field at the same time Shipping is creating the shipment (which I think I’ve resolved). That “problem” is rather unique to us, and only happened occasionally.

7

I see you are doing some division… is it possible you are getting a divide by zero?

I’d start by just copying the query. Making it a left outer join and delete all your calculated fields and just add company field from one of your upper tables and see if the query runs.

8

I’m not sure about your errors, but I know that you’ll want to handle the nulls, so I would update the above formula to this.

Sum(isnull(ShipDtl.OurInventoryShipQty.0))+ Sum(isnull(ShipDtl.OurJobShipQty,0))

If you don’t do that, you won’t be able to add anything to a null. It’s easy to think Null +15 = 15, but it doesn’t work that way, it will actually return a null, or error out. So using isnull() will replace a null with a 0 so that the calculated field can actually do the calculation. Plus, you don’t have a “filtering” effect because you will get at least a 0 for every row.

1 Like
9

@utaylor and yourself were correct. I had one calculation that wasn’t taking into consideration the zeros.

1 Like