RQL入门者求助：查询指定时间段内endDate为NULL的产品

Hey there! Let's get that query sorted out for you.

Why your original statement didn't work #

Your line endDate IS NULL BETWEEN ?1 AND ?2; has a syntax misstep. In RQL (and most SQL-like query languages), BETWEEN is used to check if a field's value falls within a defined range of two values. You can't directly combine IS NULL—a check for empty field values—with BETWEEN like this. The query parser can't interpret this mixed logic, so it won't execute as you intended.

The correct query for your goal #

You want all products where endDate is NULL, and they relate to your specified timeframe (?1 to ?2). Assuming you're using a start date field (like startDate) to anchor the product's presence in that window, here are two valid approaches:

1. If you want products with a NULL endDate that started within the timeframe:

   SELECT * FROM product
   WHERE endDate IS NULL
   AND startDate BETWEEN ?1 AND ?2;
   

2. If you want products with a NULL endDate that were active at any point during the timeframe:
   (This covers cases where the product started before the window ended and is still active)

   SELECT * FROM product
   WHERE endDate IS NULL
   AND startDate <= ?2;
   

Quick tip #

Double-check that ?1 and ?2 are formatted to match your date field's data type (e.g., '2024-01-01' for date strings) to avoid type mismatch errors.

内容的提问来源于stack exchange，提问作者Lankani