BAQ Parent xolumn in SubQuery

Epicor ERP 10
1

Hi,

Using 10.2.200.28 and would like to know how to reference a column (from a “parent” top level query) within a InnerSubQuery. Im able to write my query in T-SQL successfully, but 1 line is blocking me to do it from the BAQ editor:

	[JobProd1].[Company] as [JobProd1_Company],
	[JobProd1].[JobNum] as [JobProd1_JobNum]
from Erp.JobProd as JobProd1
inner join  (select 
	[JobProd].[Company] as [JobProd_Company],
	[JobProd].[OrderNum] as [JobProd_OrderNum]
from Erp.JobProd as JobProd
where (JobProd.Company = @CurrentCompany  and JobProd.JobNum = @JobNum))  as SubQuery2 on 
	JobProd1.Company = SubQuery2.JobProd_Company
	and JobProd1.OrderNum = SubQuery2.JobProd_OrderNum
inner join Erp.OrderRel as OrderRel on 
	OrderRel.Company = JobProd1.Company
	and OrderRel.OrderNum = JobProd1.OrderNum
	and OrderRel.OrderLine = JobProd1.OrderLine
	and OrderRel.OrderRelNum = JobProd1.OrderRelNum
	and ( OrderRel.VoidRelease = 0  )

inner join Erp.OrderDtl as OrderDtl on 
	OrderRel.Company = OrderDtl.Company
	and OrderRel.OrderNum = OrderDtl.OrderNum
	and OrderRel.OrderLine = OrderDtl.OrderLine
	and ( OrderDtl.VoidLine = 0  )

inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
	and OrderDtl.OrderNum = OrderHed.OrderNum
	and ( OrderHed.VoidOrder = 0  )

where (JobProd1.JobNum <> @JobNum)
 and not  exists (select UD101A_Character06 from (select 
	[UD101A].[Character06] as [UD101A_Character06]
from Ice.UD101A as UD101A
where (UD101A.Company = @CurrentCompany  and UD101A.Character06 <> '' 
        and (
	UD101A.Character06=JobProd1.JobNum 
	)
)) as SubQuery11)

Im unable to do the “UD101A.Character06=JobProd1.JobNum” in the editor, since JobProd1 comes from my top level query. Ive attached the BAQ I have so far if easier (change extension to .baq).

thanks in advance,

PackageLinkedJobs3 - rename extension to baq.txt (89.3 KB)

2

I didn’t open the BAQ so I’m just taking a shot in the dark, but you should be able to do what I think you are trying to do with subquery criteria. In the lower level use subquery critieria, and = specified table field value. There you can access other levels to set criteria.

image
image.png1069×649 41.3 KB
.

Also, just FYI, this forum is configured to take .baq files, so you don’t need to go through the hassle of changing the extension.

3

Thank you very much! Not sure why I didnt see it before :slight_smile: