How do I use 1 subQuery to filter the returned rows of another subquery?

shobdy · August 24, 2018, 3:39pm

Hello,

I will try my best to explain my predicament.

I am trying to return rows the contain PartTran_Company, PartTran_PartNum, PartTran_TranType, PartTran_TranDate but I need to filter it to only the latest PartTran_TranDate for each PartNum.

I was told a query that would filter only the newest date and it worked until I added the TranType field, then it returned the latest dated row for each different TranType.

How do I get the subquery to pass back the field information of TranType and TranDate without it messing up.

Hope this makes sense, any help is greatly appreciated.

Thanks,

Shawn

knash · August 24, 2018, 3:41pm

create one query to get the date and part
create another query to use date and part to join to get the trantype.

Chris_Conn · August 24, 2018, 3:41pm

Perhaps just the SubQueryCriteria?

Or you can use joins to the subquery to the main query tables

shobdy · August 24, 2018, 3:42pm

I need a bit more detailed information on how to do that?

Chris_Conn · August 24, 2018, 3:45pm

I created a subquery, with its own filters.
Then on main query click the SubQueries table icon (see mouse in screenshot) and select the subquery.
Make the logical links

shobdy · August 24, 2018, 4:09pm

@Chris_Conn,

What I am trying to add it to makes it more complicated, I think.

I created a subquery that contains the fields I want to show.
I created a subquery to show only the newest dated rows for each PartNum.
I added both queries to my main query and connected together like picture below.

I joined the PartTranFields (the one with all relevant fields to show) to Part using Comapny and Partnum.
I joined the CalcDate(query that shows only newest dated rows for each partNum) using Company, PartNum, and TranDate.

In my results, I seem to be getting duplicate entries now.

Any ideas on what I am doing wrong?

Chris_Conn · August 24, 2018, 4:14pm

Try setingt the top level query options to DISTINCT.

shobdy · August 24, 2018, 4:18pm

@Chris_Conn

When I do that it does limit it better but it doesn’t return the same amount as when I run it with just the calc date on it.

Top Level distinct returns 3802 rows.
Having just the query the returns only newest records returns 3542 rows.

What the extra 260 rows are, I have no idea.

I would feel better if it matched.
Any Ideas?

Chris_Conn · August 24, 2018, 4:19pm

When you say “same amount” do you mean # of rows? It shouldnt be the same, we are trying to limit the duplicates

shobdy · August 24, 2018, 4:22pm

I would think that the version that only had the calc date would be more accurate.

Doing distinct added 260 more rows on me. I can’t guarantee that those are the newest dated records, can I?

Chris_Conn · August 24, 2018, 4:23pm

Assuming you only marked distinct at the top level query - you should not get any new rows. Only less

knash · August 24, 2018, 4:24pm

copy and paste the query phrase.

shobdy · August 24, 2018, 4:25pm

I am comparing 2 different versions of the BAQ for the row count.

The first version Has the same top level tables except I am not added the subquery to get my PartTran_TranType field. It returned 3542 rows.

The 2nd version does have the field PartTran_TranType added to it and top level set to distinct. That returned 3802 rows.

shobdy · August 24, 2018, 4:34pm

@knash

Below is the one that added the TranType field and made it have more records than the version without it.
select distinct [Part1].[Company] as [Part1_Company], [PartTranFields1].[PartTranFields_TranDate] as [PartTranFields_TranDate], [Part1].[PartNum] as [Part1_PartNum], [RunningTotal].[Calculated_RunningTotal] as [Calculated_RunningTotal], [PartTranFields1].[PartTranFields_TranType] as [PartTranFields_TranType], [Part1].[PartDescription] as [Part1_PartDescription], [Part1].[TypeCode] as [Part1_TypeCode], (PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost) as [Calculated_TotalCost] from Erp.Part as Part1 inner join Erp.PartCost as PartCost on Part1.Company = PartCost.Company And Part1.PartNum = PartCost.PartNum

inner join (select
[PartBin].[Company] as [PartBin_Company],
[PartBin].[PartNum] as [PartBin_PartNum],
(sum( PartBin.OnhandQty )) as [Calculated_RunningTotal]
from Erp.PartBin as PartBin
group by [PartBin].[Company],
[PartBin].[PartNum]) as RunningTotal on
Part1.Company = RunningTotal.PartBin_Company
And
Part1.PartNum = RunningTotal.PartBin_PartNum

inner join (select
[PartTranFields].[Company] as [PartTranFields_Company],
[PartTranFields].[PartNum] as [PartTranFields_PartNum],
[PartTranFields].[TranType] as [PartTranFields_TranType],
[PartTranFields].[TranDate] as [PartTranFields_TranDate]
from Erp.PartTran as PartTranFields
inner join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
(max( PartTran.TranDate )) as [Calculated_Date]
from Erp.PartTran as PartTran
group by [PartTran].[Company],
[PartTran].[PartNum]) as CalcDate on
PartTranFields.Company = CalcDate.PartTran_Company
And
PartTranFields.PartNum = CalcDate.PartTran_PartNum
And
PartTranFields.TranDate = CalcDate.Calculated_Date) as PartTranFields1 on
Part1.Company = PartTranFields1.PartTranFields_Company
And
Part1.PartNum = PartTranFields1.PartTranFields_PartNum

where (Part1.InActive = 0 and Part1.QtyBearing = 1)

shobdy · August 24, 2018, 4:37pm

@knash,

The version I feel may be more accurate but doesn’t contain all the needed fields (returned 3502 rows) is:


select 
	[Part1].[Company] as [Part1_Company],
	[Part1].[PartNum] as [Part1_PartNum],
	[Part1].[PartDescription] as [Part1_PartDescription],
	[Part1].[TypeCode] as [Part1_TypeCode],
	[RunningTotal].[Calculated_RunningTotal] as [Calculated_RunningTotal],
	[CalcDate1].[Calculated_Date] as [Calculated_Date],
	(PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost) as [Calculated_TotalCost]
from Erp.Part as Part1
inner join Erp.PartCost as PartCost on 
	Part1.Company = PartCost.Company
And
	Part1.PartNum = PartCost.PartNum
inner join  (select

[PartBin].[Company] as [PartBin_Company],

[PartBin].[PartNum] as [PartBin_PartNum],

(sum( PartBin.OnhandQty )) as [Calculated_RunningTotal]

from Erp.PartBin as PartBin

group by [PartBin].[Company],

[PartBin].[PartNum])  as RunningTotal on

Part1.Company = RunningTotal.PartBin_Company

And

Part1.PartNum = RunningTotal.PartBin_PartNum
inner join  (select

[PartTran].[Company] as [PartTran_Company],

[PartTran].[PartNum] as [PartTran_PartNum],

(max( PartTran.TranDate )) as [Calculated_Date]

from Erp.PartTran as PartTran

group by [PartTran].[Company],

[PartTran].[PartNum])  as CalcDate1 on

Part1.Company = CalcDate1.PartTran_Company

And

Part1.PartNum = CalcDate1.PartTran_PartNum
where (Part1.InActive = 0  and Part1.QtyBearing = 1)

shobdy · August 24, 2018, 4:38pm

@knash,
@Chris_Conn

I can export both and paste here if it will help you understand what I am trying to do.

Let me know if you want me to do that.

Chris_Conn · August 24, 2018, 4:45pm

If it were me, I believe I’d sort by part num and export both to excel side by side and compare

shobdy · August 24, 2018, 4:59pm

Just eyballing a quick comparison, the one using distinct has duplicate part numbers in it. I have found a couple in the first 10 rows or so. Assuming it is how I got 260 more rows, so distinct didn’t help.

Any other thoughts? Do you want me to export the 3802 record one so you can look?

I really appreciate anything you can do for me.

shobdy · August 24, 2018, 5:05pm

Test_NonMovingInventory_MPD.baq (106.2 KB) - 3542 rows
NonMovingInventory_MPD.baq (106.3 KB) - 3802 rows

knash · August 24, 2018, 5:57pm

This one doesn’t bring back any duplicates.

select [Part1].[Company] as [Part1_Company], [Part1].[PartNum] as [Part1_PartNum], [Part1].[PartDescription] as [Part1_PartDescription], [Part1].[TypeCode] as [Part1_TypeCode],
[RunningTotal].[Calculated_RunningTotal] as [Calculated_RunningTotal], [CalcDate1].[Calculated_Date] as [Calculated_Date], 
(PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost) as [Calculated_TotalCost] 

--select Part1.PartNum, count(*)
from Erp.Part as Part1 
inner join Erp.PartCost as PartCost on Part1.Company = PartCost.Company And Part1.PartNum = PartCost.PartNum 
inner join (select
 [PartBin].[Company] as [PartBin_Company],
 [PartBin].[PartNum] as [PartBin_PartNum],
 (sum( PartBin.OnhandQty )) as [Calculated_RunningTotal]
 from Erp.PartBin as PartBin
 group by [PartBin].[Company],
 [PartBin].[PartNum]) as RunningTotal on
 Part1.Company = RunningTotal.PartBin_Company
 And
 Part1.PartNum = RunningTotal.PartBin_PartNum

inner join (select
 [PartTran].[Company] as [PartTran_Company],
 [PartTran].[PartNum] as [PartTran_PartNum],
 (max( PartTran.TranDate )) as [Calculated_Date]
 from Erp.PartTran as PartTran
 group by [PartTran].[Company],
 [PartTran].[PartNum]) as CalcDate1 on
 Part1.Company = CalcDate1.PartTran_Company
 And
 Part1.PartNum = CalcDate1.PartTran_PartNum

where (Part1.InActive = 0 and Part1.QtyBearing = 1)
--group by Part1.PartNum

Is this not the correct one?

Topic		Replies	Views
E10.0 - SubQuery return maximum transaction date (Bin Ex.) Epicor ERP 10	12	5685	March 24, 2022
Help filtering out particular rows in a BAQ/Dashboard Epicor ERP 10 e10	55	7101	July 8, 2019
BAQ return only 1 row per part number Epicor ERP 10	20	8911	April 3, 2024
In BAQ, How to retrieve only the latest transaction date row per partNum Epicor ERP 10	3	1566	August 24, 2018
MFG-CUS and STK-CUS Epicor ERP 10 baq	10	1275	March 29, 2024

How do I use 1 subQuery to filter the returned rows of another subquery?

Related Topics