I have a quick question that I have never been able to figure out and have not tried in sometime, but need the data now.
I am trying to run a BAQ that shows me Sales Orders without Lines. Meaning the Sales Order was created, but no lines were added.
I added the partnum field and did a criteria partnum=isnull and the report returns 0 rows. I tried adding an =empty string instead of =insnull, but that doesn’t work either.
Does anyone know if it can be done and if so, how?
You can use a subquery and filter that subquery by orderline=null
select
[SubQuery1].[OrderHed_OrderNum] as [OrderHed_OrderNum],
[SubQuery1].[OrderDtl_OrderLine] as [OrderDtl_OrderLine]
from (select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine]
from Erp.OrderHed as OrderHed
left outer join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum) as SubQuery1
where (SubQuery1.OrderDtl_OrderLine is null)
Yes, that works. That is an easy way to do what I am wanting, but I need to learn Subqueries for more complex BAQs. I am not yet live with E10 and Subqueries are new to me.