Hi All,
I am currently facing a challenge in identifying specific pages under
"/content/products/zh-cn" where the rollout date later than translation date.
To achieve this, I've attempted to construct an SQL query utilizing properties such as cq:lastRolledout and cq:lastTranslationUpdate the query does not yield the desired results.
Notably, I've encountered discrepancies in cases where the cq:lastTranslationUpdate property is absent, yet the page still requires listing based on rolledout date.
Here's the query I've attempted:
first scenario rollout date>translation date
1. SELECT [jcr:path], [cq:lastrolledout], [cq:lasttranslationupdate]
FROM [nt:base]
WHERE ISDESCENDANTNODE('/content/products/zh-cn')
AND [cq:lastrolledout] IS NOT NULL
AND [cq:lasttranslationupdate] IS NOT NULL
AND [cq:lastrolledout] > [cq:lasttranslationupdate]
2 second scenario pages don't have translationdate property
SELECT [jcr:path], [cq:lastrolledout]
FROM [nt:base]
WHERE ISDESCENDANTNODE('/content/products/zh-cn/')
AND [cq:lastrolledout] IS NOT NULL
AND [cq:lasttranslationupdate] IS NULL
Unfortunately, first query is met with a java.text.ParseException, and it fails to execute successfully. Second query not giving proper results
Could anyone kindly provide guidance or an alternative approach to address these scenarios effectively