BAQ Exists Subquery Criteria Example

Epicor ERP 10
,
1

Does anybody have a working example of how to use the Subquery Criteria Exists?

Here’s an example of how I would use it in SSMS to find parts that don’t have an approved part revision.

SELECT
    [P].[PartNum],
    [P].[PartDescription]
FROM [Erp].[Part] [P]
WHERE [P].[Company] = 'XXX'
    NOT EXISTS (
        SELECT 1
        FROM [Erp].[PartRev] [PR]
        WHERE [PR].[Company] = [P].[Company] AND
            [PR].[PartNum] = [P].[PartNum] AND
            [PR].[Approved] = 1
    )

How I’ve done this with a BAQ is to left join it and check if a column is null.

SELECT
    [P].[PartNum],
    [P].[PartDescription]
FROM [Erp].[Part] [P]
LEFT JOIN [Erp].[PartRev] [PR] ON
    [PR].[Company] = [P].[Company] AND
    [PR].[PartNum] = [P].[PartNum] AND
    [PR].[Approved] = 1
WHERE [P].[Company] = 'XXX' AND
    [PR].[Company] IS NULL

This is a simpler example, but there are times when EXISTS would be a better solution.

2

In your simple example I would do something like this:

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[ClassID] as [Part_ClassID],
	[Part].[TypeCode] as [Part_TypeCode],
	[Part].[PartDescription] as [Part_PartDescription],
	[Part].[InActive] as [Part_InActive]
from Erp.Part as Part
inner join  (select 
	[PartRev].[Company] as [PartRev_Company],
	[PartRev].[PartNum] as [PartRev_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[PartRev].[Approved] as [PartRev_Approved]
from Erp.PartRev as PartRev
where (PartRev.Approved = true))  as SubQuery2 on 
	Part.Company = SubQuery2.PartRev_Company
	and Part.PartNum = SubQuery2.PartRev_PartNum
where (exists (select 
	[PartRev].[Company] as [PartRev_Company],
	[PartRev].[PartNum] as [PartRev_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[PartRev].[Approved] as [PartRev_Approved]
from Erp.PartRev as PartRev
where (PartRev.Approved = true)))

Switch to a left join if you want all the parts. Keep as inner join to show only parts with approved revs.

1 Like
3

Please correct me if I’m wrong, but your exists clause would evaluate to TRUE if there are any approved revs in the system, not just the current Part record because there’s nothing to tie it to the current Part record. The inner/left join does make sense to me because the ON clause dictates how the inner subquery is tied to the Part table.

4

I will admit I am no expert in SQL. I think that the join above takes care of the link you are worried about. At least in my quick mock-up it seemed to pull the right data. Hopefully someone that knows SQL better can comment.

5

There is EXISTS operation in BAQ criteria, why don’t you just use it?

6

The way it looks in the BAQ Designer, you can only check if there are any rows in the entire subquery, there’s no way to link it to the current row being evaluated.

Here’s some sample data to help illustrate the rows that the two queries would return, just from reading the queries.

Part table:

| PartNum | PartDescription |
|---------|-----------------|
| A       | Part A          |
| B       | Part B          |
| C       | Part C          |

PartRev table:

| PartNum | RevisionNum | Approved |
|---------|-------------|----------|
| A       | 1           | 1        |
| B       | 2           | 0        |

SSMS NOT EXISTS:

SELECT
    [P].[PartNum],
    [P].[PartDescription]
FROM [Erp].[Part] [P]
WHERE [P].[Company] = 'XXX'
    NOT EXISTS (
        SELECT
            [PR].[Company],
            [PR].[PartNum],
            [PR].[RevisionNum]
        FROM [Erp].[PartRev] [PR]
        WHERE [PR].[Company] = [P].[Company] AND
            [PR].[PartNum] = [P].[PartNum] AND
            [PR].[Approved] = 1
    )

BAQ NOT EXISTS (from BAQ Designer General tab):

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription]
from Erp.Part as Part
where (not exists (select 
	[PartRev].[Company] as [PartRev_Company],
	[PartRev].[PartNum] as [PartRev_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum]
from Erp.PartRev as PartRev
where (PartRev.Approved = 1)))

BAQ NOT EXISTS (from SQL Profiler (reformatted)):

SELECT
    [Part].[PartNum] [Part_PartNum],
    [Part].[PartDescription] [Part_PartDescription]
FROM [Erp].[Part]
WHERE NOT (
        EXISTS (
            SELECT
                1
            FROM (
                SELECT
                    [PartRev].[Company] [PartRev_Company],
                    [PartRev].[PartNum] [PartRev_PartNum],
                    [PartRev].[RevisionNum] [PartRev_RevisionNum]
                FROM [Erp].[PartRev]
                WHERE
                    ([PartRev].[Approved] = 1 AND
                    (
                        [PartRev].[Company] IS NULL OR
                        [PartRev].[Company] = N'' OR
                        [PartRev].[Company] = @CurrentCompany)
                    ) AND
                    (
                        [PartRev].[Plant] IS NULL OR
                        EXISTS (
                            SELECT
                                1 [c1]
                            FROM [Ice].[SysUserComp] [SysUserComp]
                            WHERE [SysUserComp].[Company] = [PartRev].[Company] AND
                                [SysUserComp].[UserID] = @CurrentUserID AND
                                Ice.lookup(PartRev.Plant, SysUserComp.PlantList, '~') > 0
                        )
                    )
            ) ApprovedPartRevs
        )
    ) AND
    (
        [Part].[Company] IS NULL OR
        [Part].[Company] = N'' OR
        [Part].[Company] = @CurrentCompany
    )

Evaluations:

| PartNum | PartDescription | RevisionNum | Approved | SSMS NOT EXISTS Row Returned | BAQ NOT EXISTS Row Returned |
|---------|-----------------|-------------|----------|------------------------------|-----------------------------|
| A       | Part A          | 1           | 1        | FALSE                        | FALSE                       |
| B       | Part B          | 2           | 0        | TRUE                         | FALSE                       |
| C       | Part C          | NULL        | NULL     | TRUE                         | FALSE                       |

With the BAQ (not) EXISTS subquery operation, it doesn’t return any rows because Part A has an Approved revision, whereas the intention was to return Part B and Part C because they don’t have any approved revisions.

It seems like this is just how EXISTS works in BAQs, but I was hoping that I was using it wrong and somebody has an example of using it correctly. I can usually get the results I want using a LEFT JOIN and checking if a column IS NULL and sometimes using DISTINCT in the subquery, but there are times when using EXISTS would be the better solution.

7

I may be wrong but you need to add correlation between subqueries. the criteria you have here:

you need to go to the second subquery (PR) and add Where clause there that point to the first subquery (P)

1 Like
8

Ahhh, now I see how to do it! I didn’t know you could specify a column from a different subquery in the SubQuery Criteria. Thank you @Olga!

Here’s some screenshot for reference:

image
image1000×652 42.7 KB

image
image1000×652 42.4 KB

image
image1001×654 57.6 KB

This gives this query in the General tab:

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription]
from Erp.Part as Part
where (not exists (select 
	[PartRev].[Company] as [PartRev_Company],
	[PartRev].[PartNum] as [PartRev_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum]
from Erp.PartRev as PartRev
where (PartRev.Approved = 1)
 and (PartRev.Company = Part.Company  and PartRev.PartNum = Part.PartNum)
))
1 Like