BAQ Query question

Is there a way to compare 2 tables and see what is not common in both tables? What i want to do is compare the jobmtl table parts to partmtl table parts and see which part from partmtl table is not used in the actual job (job mtl).
I created a list of jobs that i closed for a particular date using JobHead table (toplevel table)
i then linked jobmtl table to Jobhead table to get all the parts on the job (level 1)

Now i want to compare those job material parts to Partmtl table (bom level 1) to see which component part on the Partmtl is not used on the job.

You are looking for a Left Join where the Left Join is null
Join PartMtl to JobMtl on a Left Join and in the Subquery criteria set JobMtl.partNul isNull

1 Like

When I compare two tables, I create a UNION query with the items I want to compare (Company, JobNum, MtlPartNum, etc), In Table A I create a calculated numeric field with the value of 1. In Table B, I create the same numeric calculated field with the value 2. Finally, I groups the two tables and sum the calculated fields grouped by the key fields. All records with a 3 are in both tables; all records with a 2 exist only in the second table, and items with a 1 only exist in the first table.

Mark W.

That sounds like a good idea, is that faster performance wise (or just a better practice) than doing an outer join and looking for nulls?

Outer join with nulls just shows you one way. I don’t think you can do both a left AND right outer join so you would miss from table A using a left and miss parts from table B using a right.

Can’t speak to performance on the Union though… Basically, you’re just setting a bit (first digit for table A and second digit for table B). With the amount of data, I think it would be quick - even for a couple of thousand parts.

Mark W.

Isn’t that what this is?

image

Well, there you go. I never saw that before. That makes it easier then. Thanks!

Mark W.

Thanks guys for the input/ ideas. The problem i run into is it seems like i need to link partmtl tableto both tables (Jobhead.Partnumber to PartMtl.Partnum for parent and partmtl.mtlpartnum to Jobmtl.partnum for components).

When i link partmtl to both tables, i get kicked out of epicor and need to log back in.

I wanted to try to “Union” method, but i am not that familiar with using the Union clause. So far, i have only used the “TopLevel” and “InnerSubQuery” features of subquery.

I am also wondering if the “Intersect” and “Except” sub queries could do for you… I have never played with them, so I am just throwing them out… if you do an internet search for Sql Intersect, you can read all about them.
image

1 Like

You’ll have to do a union to combine your JobAsm and JobMtl table. Those are separate on a job, but not in the master (PartMtl). You shouldn’t need the JobHead just to get part number (it will be in the JobAsm table). Then when those are combined, you can join that with your PartMtl table.

To get a BOM from the PartMtl table, you will probably need a CTE query. Take a look at this how to in order to do that. (you’ll need access to epic web to see this)

https://epicweb.epicor.com/Education/OnlineHelpFeatureSummary/Epicor%20ERP%2010/10.1.400/Help/enu/Standard/Tools_BAQs/CaseStudy.CTE.SelectColumns.html

Tim,

I used an Except query today to compare the contents of the part tables in two companies. The Except displayed all parts which were missing from the child company, which we used for a quick DMT load.

When you use the Except, I believe it compares the values of all fields displayed in the query. If used for cross company comparison, remove the company field or you will return every record.

This was quick and easy.

2 Likes

How did you do the links when you did the “Except” clause in the BAQ?

I have Top table as partmtl and jobhead.
On the subquery table, i have jobmtl table with “Except” clause.

I can’t get past this because everytime i try to link these two queries, i get kicked off epicor and need to log back in. i am sure i am not doing my join correctly.

Is this a one time query or something you need to do long term or let a user run?

If it is a one time query copy live to test and create the query you want in sql - you will not be constrained by a baq - my guess is you would create a temp table to do the lookup one way and another temp table to do the lookup the other way and then merge then with a union - key point copy live to test and if you have a sandbox/test environment do it on there.

I would start with a very simple example when trying the Except logic… and build UP to what you want… sometimes taking a more complex Query and adding on something new like a CTE, Except, Union, etc can be complicated…
I know that one rule for Union queries (and I believe Except queries as well) is that the number of columns and column types must be the same between the two queries.

The tables are joined like a Union query. Tim’s right, they have to have the same fields, ad they need to be matched.

Create the top level query and add the display fields.
Create a subquery, Type is Except.
Add the display fields, use the field at the bottom of the screen. it tells you which you need to match next.

You will notice that you cannot add the subquery to join the table in top level query.

select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[ClassID] as [Part_ClassID],
[Part].[ProdCode] as [Part_ProdCode],
[Part].[IUM] as [Part_IUM],
[Part].[PUM] as [Part_PUM],
[Part].[SalesUM] as [Part_SalesUM],
[Part].[ISOrigCountry] as [Part_ISOrigCountry],
[Part].[HTS] as [Part_HTS],
[Part].[SchedBcode] as [Part_SchedBcode],
[Part].[PurchasingFactor] as [Part_PurchasingFactor],
[Part].[NAFTAOrigCountry] as [Part_NAFTAOrigCountry],
[Part].[NAFTAProd] as [Part_NAFTAProd],
[Part].[NAFTAPref] as [Part_NAFTAPref],
[Part].[UOMClassID] as [Part_UOMClassID],
[Part].[PricingFactor] as [Part_PricingFactor],
[Part].[PricingUOM] as [Part_PricingUOM]
from Erp.Part as Part
where (Part.Company = ‘100’ and Part.InActive = FALSE)
except
select
[Part1].[PartNum] as [Part1_PartNum],
[Part1].[PartDescription] as [Part1_PartDescription],
[Part1].[ClassID] as [Part1_ClassID],
[Part1].[ProdCode] as [Part1_ProdCode],
[Part1].[IUM] as [Part1_IUM],
[Part1].[PUM] as [Part1_PUM],
[Part1].[SalesUM] as [Part1_SalesUM],
[Part1].[ISOrigCountry] as [Part1_ISOrigCountry],
[Part1].[HTS] as [Part1_HTS],
[Part1].[SchedBcode] as [Part1_SchedBcode],
[Part1].[PurchasingFactor] as [Part1_PurchasingFactor],
[Part1].[NAFTAOrigCountry] as [Part1_NAFTAOrigCountry],
[Part1].[NAFTAProd] as [Part1_NAFTAProd],
[Part1].[NAFTAPref] as [Part1_NAFTAPref],
[Part1].[UOMClassID] as [Part1_UOMClassID],
[Part1].[PricingFactor] as [Part1_PricingFactor],
[Part1].[PricingUOM] as [Part1_PricingUOM]
from Erp.Part as Part1
where (Part1.Company = @ExternalCompany)

2 Likes

Thanks Bryan for the example. I think i got it working now. i tried it for a single job which i know had different material used in the job vs bom and it showed me the difference.

I wanted to create the query to run once in a while myself to analyze mfg variances from the material side. The labor variance, i am able to pinpoint and analyze but always had trouble when there was material variance. i had to run the production detail report and then run the BOM costed report and look for which part was different in both reports. With this query, it will show me all the parts that we issue different material than BOM en mass.

Thank you all for your help. The error i was making before was that i did not have the same fields for top level and subquery before and i did not pay attention to the bottom screen where you could match fields from both screens. Once i pulled same fields from partmtl and jobmtl, the query ran fine.