BAQ with 3 table criteria fails

Kinetic ERP
1

I’m working on a BAQ to pull out jobs that are either open or have been closed in the last 30 days and have a specific part number. It hasn’t been working correctly so I’ve broken it down to its simplest form.
I query the JobHead table and add each criteria and test the results. When I add any two criteria the results are correct but when I add the third criteria it blows up and returns hundreds of rows. It’s like it ignores the 3rd one. Anyone else ever see this? I’ve tried rearranging the order of the criteria but it doesn’t matter 3 criteria seems too much for the BAQ.
Here’s my query:

 * 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 
	[JobHead].[JobClosed] as [JobHead_JobClosed],
	[JobHead].[ClosedDate] as [JobHead_ClosedDate],
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[DueDate] as [JobHead_DueDate],
	[JobHead].[ProdQty] as [JobHead_ProdQty],
	[JobHead].[QtyCompleted] as [JobHead_QtyCompleted]
from Erp.JobHead as JobHead
where (JobHead.JobClosed = false  or JobHead.ClosedDate > dateadd (day, -30, Constants.Today)  and JobHead.PartNum = '8002-0010')```
2

I think you might need some parenthesis in the last where statement. This is generated by the conditions you set on the table. In that area make sure you use parenthesis to separate your OR and AND statements.

where ((JobHead.JobClosed = false  or JobHead.ClosedDate > dateadd (day, -30, Constants.Today))  and JobHead.PartNum = '8002-0010')
2 Likes
3

Thanks! That did the trick. I haven’t used the parenthesis before when adding criteria. As a general rule, do I want to use them to separate my OR statements from my AND statements?

4

Yes, think about it logically to determine where you want the parenthesis. (a AND b) OR c, is different than a AND (b OR c)

5

Yes, SQL (and BAQ’s copy a lot from SQL) doesn’t prioritize AND over OR, so you have to use parens to specify the order of execution.

6

When you get beyond like 3 criteria, a trick I use is to make a calculated field called “DisplayThisRow” or something like that. Then filter the query by THAT field.

Or here is one where I want to show parts set up “wrong” per our policies. I only display what is not “OK” - and I also happen to show that message:

The field:

image
image784×589 32.6 KB

The filter:
image

Edit: Oh good - I have a pic of the prior version of this same BAQ. WAY too many criteria, and so when I had to add one more, I kept breaking it. That’s when I overhauled this portion. And adding the error message is a big improvement, so that the user isn’t hunting for the problem.

Ignore the highlighting - it’s for whatever the ticket was about, I guess.

image
image908×341 19.9 KB

1 Like
7

Cool idea! I think I have done this too and not realized it! I like the way you worded it so this process can be useful for lots of different cases.

1 Like
8

Yes, I am the same - I melt my brain working through “Do I need another parenthesis here? Did I miss one?”

I finally made this my go-to method now. It’s way easier to debug.