Looking to build a dashboard - SQL Help

Kinetic ERP
1

I’m trying to build a DB that gives me all of the approved part revisions with inactive material parts and list the approved part and inactive material. I have written the following but it is giving me approved revs and inactive material in every/any revision.

SELECT
PM.PartNum’td’,‘’, P2.InActive’td’,‘’, PM.MtlPartNum’td’,‘’, P1.InActive’td’,‘’, PR.RevisionNum’td’,‘’, P2.PartDescription’td’,‘’
from Kinetic.ERP.PartRev PR
inner join Kinetic.ERP.PartMtl PM on PR.Company = PM.Company and PR.PartNum = PM.PartNum
inner join Kinetic.ERP.Part P1 on P1.Company = PM.Company and P1.PartNum = PM.MtlPartNum
inner join Kinetic.ERP.Part P2 on P2.Company = P1.Company and P2.Partnum = PR.PartNum

where PR.Company = ‘US’
and PR.Approved = ‘1’
and P1.InActive <> ‘0’
and P2.InActive = ‘0’

I’m relatively new to SQL so any help is appreciated.

2

It is hard for me to read SQL, so I just built what you asked for, ignoring your code. This is pretty straight forward. I setup a subquery that returns the inactive materials. Then linked that to the part revs and filtered the revs to only show approved. Let me know if this gives you the expected results.
InactiveMats.baq (30.4 KB)
Good luck!

1 Like
3

I really appreciate this, Nate. As you can tell, I’m pretty new at this. Unfortunately, the BAQ won’t import due to your version being later than 4.2.100.0. Would you mind posting a screenshot of it and I can just copy your work? haha

4

The in-app help has a section on how to build recursive BAQ’s and it literally uses BOM navigation as the example case. Search for “Common Table Expression Query” in the F1 help. Just add a filter to remove active parts from the results.

For future reference, I’d distinguish between BAQ and SQL queries. While there’s overlap, I wouldn’t ever say they were the same thing.

5

You can try –
Saving the BAQ file with a .rar extension → Open Archive and change the BAQ version file to match your version and saving it.
I have tried this and it has worked for me to kind of implement backwards compatibility.
I am on 10.2.700

6

I didn’t do any recursion in this BAQ. Here’s the SQL, if you can build it from this:

select 
	[PartRev].[PartNum] as [PartRev_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[SubQuery2].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum]
from Erp.PartRev as PartRev
inner join  (select 
	[PartMtl].[PartNum] as [PartMtl_PartNum],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part on 
	PartMtl.Company = Part.Company
	and PartMtl.PartNum = Part.PartNum
	and ( Part.InActive = true  ))  as SubQuery2 on 
	PartRev.PartNum = SubQuery2.PartMtl_PartNum
	and PartRev.RevisionNum = SubQuery2.PartMtl_RevisionNum
where (PartRev.Approved = true)