Hello, I'm having an issue with the extended query range syntax. I'm currently trying to generate a query that shows retail discounts based on a starting and ending date range.
The range looks like this.
qbdsStandard = SysQuery::findOrCreateDataSource(qStandard, tableNum(RetailPeriodicDiscount));
SysQuery::findOrCreateRange(qbdsStandard, fieldNum(RetailPeriodicDiscount, ValidFrom))
.value(strFmt(
@'(
(!((RetailPeriodicDiscount.%1 < %3) && (RetailPeriodicDiscount.%2 > %4)))
&& (!((RetailPeriodicDiscount.%1 > %3) && (RetailPeriodicDiscount.%2 < %4)))
&& (!((RetailPeriodicDiscount.%1 > %3) && (RetailPeriodicDiscount.%1 > %4)))
&& (!((RetailPeriodicDiscount.%2 < %3) && (RetailPeriodicDiscount.%2 < %4)))
)',
fieldStr(RetailPeriodicDiscount, ValidFrom),
fieldStr(RetailPeriodicDiscount, ValidTo),
queryValue(startDate),
queryValue(endDate)
));
They query will run fine, however an infolog message appears saying "Query extended range failure: The '!' operator can only be used with the 'like' keyword." The range is also ignored. However, if I call toString() on the query and conver the resulting X++ sql into "real" SQL the query and range work fine.
X++ SQL (except * is actually a list of all the fields)
SELECT *
FROM RetailPeriodicDiscount(RetailPeriodicDiscount_1)
WHERE ((DateValidationType = 1)) AND (((
(!((RetailPeriodicDiscount.ValidFrom < "3/21/2015") && (RetailPeriodicDiscount.ValidTo > "3/24/2015")))
&& (!((RetailPeriodicDiscount.ValidFrom > "3/21/2015") && (RetailPeriodicDiscount.ValidTo < "3/24/2015")))
&& (!((RetailPeriodicDiscount.ValidFrom > "3/21/2015") && (RetailPeriodicDiscount.ValidFrom > "3/24/2015")))
&& (!((RetailPeriodicDiscount.ValidTo < "3/21/2015") && (RetailPeriodicDiscount.ValidTo < "3/24/2015")))
)))
JOIN * FROM RetailPeriodicDiscountLine(RetailPeriodicDiscountLine_1)
ON RetailPeriodicDiscount.OfferId = RetailPeriodicDiscountLine.OfferId
JOIN * FROM RetailGroupMemberLine(RetailGroupMemberLine_1) ON RetailPeriodicDiscountLine.RetailGroupMemberLine = RetailGroupMemberLine.RecIdJOIN * FROM EcoResProduct(EcoResProduct_1) ON RetailGroupMemberLine.Product = EcoResProduct.RecId
I then convert it (by hand) into what I think would be the equivalent sql.
REAL SQL
SELECT *
FROM RetailPeriodicDiscount d
JOIN RetailPeriodicDiscountLine dl
ON d.OfferId = dl.OfferId
JOIN RetailGroupMemberLine gml
ON dl.RetailGroupMemberLine = gml.RecId
JOIN EcoResProduct p
ON gml.Product = p.RecId
WHERE ((d.DateValidationType = 1))
AND (((
(not((d.ValidFrom < '3/21/2015') and (d.ValidTo > '3/24/2015')))
and (not((d.ValidFrom > '3/21/2015') and (d.ValidTo < '3/24/2015')))
and (not((d.ValidFrom > '3/21/2015') and (d.ValidFrom > '3/24/2015')))
and (not((d.ValidTo < '3/21/2015') and (d.ValidTo < '3/24/2015')))
)))
Basically, the real sql executes correctly, and the correct rows are returned with the date ranges. However, the X++ sql just returns everything with absolutely no filtering. Is there a way to do this in X++? Am I overlooking something?
