Skip to Content
Jul 08, 2020 at 07:21 PM

SAP B1 - Build query from line items in INV1 with common UDF


Hi there,

I am trying to build a query containing line items from the INV1 table in combination with some items of the OINV table. I managed to get a simple extract of the information I am looking for but my SQL skills are limited at best.

To simplify what I am looking for, I attached a small visual from Excel. Though I do not need the total sum on the query but can calculate the sum manually later on. Its just meant for illustration purpose. The line item prices are more relevant.

In essence, I am looking for a query, which returns all line items within the same batch number, if that batch contains Product A only. The INV1 might have other batches with other products, but the main functionality is to filter selective products and their relating customisations from an invoice. The result in the example above would return rows 1-3 and 7-8 with the relevant quantities and prices.

Thanks everybody in advance and I hope I am not too cryptic.