In the below query i am expecting only one record where date between “06-1 to 06-02” but it retrieves both the record.
second condition is not satisfied .
SELECT ARRAY token FOR token IN t.nokep
WHEN token.category = “SH” AND token.type=“01” AND token.acykor =“8080” END as pulList
FROM PREF_INT t
WHERE t.secValue=“287”
AND ANY v IN nokep SATISFIES v.category=“SH” AND v.type=“01”
AND ( (“2019-06-01” BETWEEN v.startDate AND v.endDate)
OR ( “2019-06-09” BETWEEN v.startDate AND v.endDate))
END
WHERE clause only tells if document is qualified or not.
The condition of ARRAY construct is different. If you need date condition there also you need to add that inside WHEN clause too.
SELECT ARRAY token FOR token IN t.nokep
WHEN token.category = “SH” AND token.type=“01” AND token.acykor =“8080” AND
( (“2019-06-01” BETWEEN token.startDate AND token.endDate)
OR ( “2019-06-09” BETWEEN toekn.startDate AND token.endDate)) END as pulList
FROM PREF_INT t
WHERE t.secValue=“287”
AND ANY v IN nokep SATISFIES v.category=“SH” AND v.type=“01”
AND ( (“2019-06-01” BETWEEN v.startDate AND v.endDate)
OR ( “2019-06-09” BETWEEN v.startDate AND v.endDate))
END
I don’t think the date part is required two time in this case , the below query should also work fine…
what do you think ?
SELECT ARRAY token FOR token IN t.nokep
WHEN token.category = “SH” AND token.type=“01” AND token.acykor =“8080” AND
( (“2019-06-01” BETWEEN token.startDate AND token.endDate)
OR ( “2019-06-09” BETWEEN toekn.startDate AND token.endDate)) END as pulList
FROM PREF_INT t
WHERE t.secValue=“287”
AND ANY v IN nokep SATISFIES v.category=“SH” AND v.type=“01”
END