Solved

Display Specific BAQ Results

  • 1 July 2021
  • 5 replies
  • 616 views
M

I’m attempting to build a BAQ that only displays parts with multiple approved revisions.  I built a basic BAQ that displays parts and revisions from the PartRev table.  I’m not certain how to filter these results so it only displays parts with an approved A and B revision.  I’ve attempted to do this via subqueries and subquery criteria but am unable to get the results I’m looking for.  Has anyone done something similar that could point me in the right direction?

 

select

                [PartRev].[PartNum] as [PartRev_PartNum],

                [PartRev].[RevisionNum] as [PartRev_RevisionNum],

                [PartRev].[Approved] as [PartRev_Approved]

from Erp.PartRev as PartRev

where (PartRev.PartNum like 'dp%')

 

I would like the query to display only the highlighted rows.

 

icon

Best answer by vshevchenko 1 July 2021, 17:06

View original

5 replies

J
Userlevel 2

You can add a subquery to count the number of revisions on a part (pay attention to alt methods), and then filter your main query for any with a count greater than one.

 

Joe

fred
Userlevel 3

Like Joe says.  Create a subquery returning just partnum where partnum like ‘dp%’ and Approved = 1, group by PartNum and put a Count calculated field in that subquery. On that subquery put in a where count > 1.

Join that subquery to your first query on partnum.  Now you’ll get the information you want.

Fred Zelhart, CodaBears, Inc.
V
Userlevel 1

You can do it in one query by using grouping by PartNum, filter by Approved=true and using Having (calculated field count(*) greater than 0). Of course you will not be able to show the actual revisions, because you grouping by Part Number, but you can shoe MIN(RevisionNumber) and MAX(RevisionNumber) if you want:

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
    [PartRev].[PartNum] as [PartRev_PartNum],
    (count(*)) as [Calculated_NumActiveRevs],
    (MIN(PartRev.RevisionNum)) as [Calculated_MinRev],
    (MAX(PartRev.RevisionNum)) as [Calculated_MaxtRev]
from Erp.PartRev as PartRev
where (PartRev.Company = @CompanyID  and PartRev.Approved = true)
group by [PartRev].[PartNum]
having (count(*)) > 1

HAVING is setup in the SubQuery Criteria TAB, even though there is just one query, AB name can be misleading ;-)

 

M

First of all, thank you for all the input.  I seem to have a misunderstanding of how to properly utilize subqueries.  I created SubQuery2 for the same PartRev table and criteria.  It’s displaying the PartNum field and a ‘Count’ calculated field.

 

I, then, created a SubQuery Criteria for SubQuery2 that states ‘Calculated.Count > 1’.

 

The queries must not be joined properly because I’m getting the same results as the single query.

 

V
Userlevel 1

Having is not checked on your screen shot. 

Reply


Terms & Conditions