BAQ - How to show part# even if sum qty ship is 0

Kinetic ERP
1

I have a BAQ that shows total shipped qty for list of static parts. However some of these parts have 0 shipped qty but I still want them in the results and their summed qty to be “0”. I have found that with parts that have not been sold yet (usually a product that is new or in the works of being released) do not appear in the results at all. How do I go on about this?

My BAQ is below:

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select distinct
	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[Part].[CatalogItem_c] as [Part_CatalogItem_c],
	[Part].[InActive] as [Part_InActive],
	(SUM(InvcDtl.ExtPrice)) as [Calculated_Revenue],
	(sum(InvcDtl.OurShipQty)) as [Calculated_Quantity_Sold],
	[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
	(sum(InvcDtl.SellingShipQty)) as [Calculated_selling_ship_qty]
from Erp.InvcHead as InvcHead
right outer join Erp.InvcDtl as InvcDtl on 
	InvcHead.Company = InvcDtl.Company
	and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
right outer join Erp.Part as Part on 
	InvcDtl.Company = Part.Company
	and InvcDtl.PartNum = Part.PartNum
	and ( Part.PartNum in ('13245', '23003-1', '23005-1', '23006-1', '23011-1', '23019-1', '23029-1', '23030-1', '23032-1', '23033-1', '24002', '24003', '24007', '24061', '25024', '25030', '25067', '25071', '25072', '25073', '25074', '25075', '25087', '25097', '25098', '25105', '25106', '25108', '25109', '26015-10', '26015-25', '26015-50', '27004', '27005', '27009', '28001-2', '28001-4', '28001-5', '28040', '28041', '28042', '28043', '28043-3', '28049', '28088', '28115', '28126', '28128', '28131', '28206', '28207', '28224', '28225', '28226', '28227', '28228', '28259', '28261', '28317', '28337', '28338', '28343', '28353', '28354', '28388', '28405', '28421', '28422', '28423', '28424', '28425', '28426', '28427', '28428', '28429', '28455', '28460', '28461', '28462', '28463', '28464', '28469', '28470', '28473', '28477', '28478', '28479', '28480', '28481', '28512', '28521', '28522', '28523', '28527', '28535', '28536', '28537', '28542', '28543', '28544', '28547', '28548', '28549', '28565', '28566', '28567', '28575', '28576', '28577', '28578', '28584', '28585', '28586', '28587', '28595', '28596', '28600', '28601', '28611', '28612', '28613', '28614', '28615', '28616', '28617', '28618', '28625', '28631', '28632', '28633', '28636', '28637', '28657', '28658', '28661', '28662', '28663', '28664', '28665', '28666', '28667', '48010', '48011', '48013', '48014', '48015', '48016', '48017', '48018', '48019', '48024', '48025', '48026', '48027', '48028', '48029', '48030', '48031', '48032', '48033', '48034', '50017', '50018', '50020', '50022', '50025', '50028', '50032', '50033', '50034', '50035', '50036', '50039', '50040', '50041', '50042', '66099', '66105', '66122', '66123', '66124', '66125', '66238', '66253', '66254', '76001', '76002', '76009', '76040-1', '76040-2', '76041-1', '76041-2', '76041-3', '76041-4', '76058-1', '76058-2', '76059-1', '76059-2', '76059-3', '76060-1', '76060-2', '76060-3', '76061-1', '76075', '76076', '76089-1', '76089-2', '76094-3', '76094-4', '79010', '79011', '79012', '79013', '79020', '79027', '79037', '79038', '79039', '79040', '79050', '79091', '79095', '79152', '79180', '79197', '79198', '79207', '79220', '79221', '79222', '79227', '79228', '79232', '79239', '83009', '83010', '83011', '83012', '83013', '83014', '83015', '83016', '83017', '83018', '83020', '83021', '83024', '83025', '99030-1', '99030-2', 'SV012', 'SV015')  )

where (InvcHead.InvoiceDate >= '1/1/2022')
group by [Part].[PartNum],
	[Part].[PartDescription],
	[Part].[CatalogItem_c],
	[Part].[InActive],
	[InvcHead].[InvoiceDate]
2

I’m not positive, but I think you may need to change your calculated field selling_ship_qty to:

sum(CASE WHEN InvcDtl.SellingShipQty is Null THEN 0 ELSE InvcDtl.SellingShipQty )
3

Hi Kevin, I tried this and I get an error near syntax “)”

image
image875×538 61.1 KB

Does this look OK? Not sure what I am missing

4

You don’t have an End on your case statement

5

After you fix the syntax error, I’m not sure you’re going to get the result you’re looking for, though I could be wrong…

If the part hasn’t shipped yet, then it hasn’t been invoiced and won’t be in the invoice tables you are using.

1 Like
6

An easier way of writing this is:

isnull(InvcDtl.SellingShipQty,0)

Just makes it a bit easier rather than reading using a case statement.

1 Like
7

Good to know!

8

Your primary table should be Part, not InvcHead & do a left join from Part to InvcDtl (and then join that to InvcHead with a left join.

That will give you ALL the parts meeting your criteria on PartNum and if they have InvcDtl records to sum, it will display it. If they don’t, it will display NULL in the results.