SQL-2 Query not returning correct result in ACS AEM Commons report

[sangrampatil111]

Hi all,

When below query executed in crx/de, it returns all expected results. But when ran through ACS AEM commons, returning less result.

SELECT page.[cq:lastReplicated] FROM [cq:PageContent] AS page WHERE ISDESCENDANTNODE(page, '/content/your-path') AND page.[cq:lastReplicated] IS NOT NULL

Basically "cq:lastReplicated" is causing issue

[EstebanBustamante]

Hi, 

In which ACS commons feature are you running this query? Is this something that you are inserting? or something you troubleshoot? Maybe there are some extra configurations missing in the ACS commons features?

[sangrampatil111]

Hi, 

I am using this query in the Report feature.

[arunpatidar]

Hi @sangrampatil111 
Can you try with below query

SELECT * FROM [cq:Page] AS page WHERE ISDESCENDANTNODE(page, '/content/your-path') AND page.[jcr:content/cq:lastReplicated] IS NOT NULL

[sangrampatil111]

Hi @arunpatidar,

It is returning zero results 

[sangrampatil111]

The query is working fine on my local setup in ACS Commons reports. It is not working correctly on all project instances in reports. It is working fine in crx/de. 

Please let me know what needs to check. 

[arunpatidar]

Hi @sangrampatil111 
You can check the error logs, the issue could be the number of pages on project instances too big and query could not traverse.

Try also with small set of pages.

[kautuk_sahni]

@sangrampatil111 Did you find the suggestion helpful? Please let us know if you require more information. Otherwise, please mark the answer as correct for posterity. If you've discovered a solution yourself, we would appreciate it if you could share it with the community. Thank you!