BAQ with date range

aaronssh · December 12, 2019, 8:07pm

I have a BAQ that I want to filter by date range. To do this I add a parameter StartDate and filter by OrderHed.OrderDate>=StartDate. When I test my query, it works at this point.

The problem comes when I add a second parameter EndDate and a second filter OrderHed.OrderDate<=EndDate. The filter looks like this:

And the error “Not enough actual parameters” looks like this:

I’ve noticed that if I change the second filter to be a different field, for example OrderHed.DueDate<EndDate then everything works fine.

Any ideas on how to make this work? Or is filtering by row twice with two different parameters just not supported? Probably worth mentioning this is an external BAQ, not sure if that’s related.

Chris_Conn · December 12, 2019, 8:11pm

Not sure if that’s an issue or not but I do this quite often in internal BAQs.

ckrusen · December 12, 2019, 8:23pm

You could try to coerce the SQL to use a BETWEEN operator on the WHERE phrase.

aaronssh · December 12, 2019, 8:31pm

How do I get a BETWEEN operator in there? It’s not in the list of operations to select from and I can’t edit the BAQ SQL directly.

ckrusen · December 12, 2019, 8:36pm

This might be really bad form (if so I hope someone corrects me!).

But you can make a malformed expression include extra SQL commands.

I have two params @StartNum and @EndNum
Added a table criteria of
Company = expression

And in the expression, I put

'MC' AND OrderHed.OrderNum BETWEEN @StartNum AND @EndNum

The resulting SQL is

select 
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate]
from Erp.OrderHed as OrderHed
where (OrderHed.Company = 'MC' AND OrderHed.OrderNum BETWEEN @StartNum AND @EndNum)

The Company = ‘MC’ could have been anything that always returns the record

I’ve effective added the criteria AND ...

aaronssh · December 12, 2019, 8:36pm

Good to know! Thx

aaronssh · December 12, 2019, 8:59pm

Calvin, this is genius, I see what you’re doing there. Very clever. I will definitely use that in the future.

I can see that your example works on a regular BAQ but I still get the same error on the External BAQ. Apparently my external query middleware (OpenLink Virtuoso) can’t handle two parameters for the same row.

Tomorrow I am going to try to see if there is a way that I can do this with two calculated fields. Any other ideas are welcome.