Expand my Community achievements bar.

Enhance your AEM Assets & Boost Your Development: [AEM Gems | June 19, 2024] Improving the Developer Experience with New APIs and Events
SOLVED

Need help on SQL2 under AEM 6.5

Avatar

Level 2

Hello All,

 

We are having lot of folders under AEM / DAM / Assets folder. To retrieve the folders we are firing a query under SQL2 through CRDXLite.

"select * from [nt:unstructured] where ISDESCENDANTNODE([/content/dam/all-campaigns]) AND [OWNEDBY]='{name}' and [CAMPAIGNNAME] IS NOT NULL order by [CAMPAIGNNAME]ASC

But when we execute this query we are getting an error " The query read or traversed more than 100000 nodes. To avoid affecting other tasks, processing was stopped". (snapshot attached for reference with the error).

 

sql query limit error.png

 

But if we fire "select * from [dam:Asset]" it returns the result with more that 100000 records.

Not sure what we are missing here. Can someone please help.

 

Thanks you all in advance.

Regards.

 

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Try to improve query with exact node name or path

e.g.

select * from [dam:Asset] where ISDESCENDANTNODE([/content/dam/all-campaigns]) AND [jcr:content/metadata/OWNEDBY]='{name}' and [jcr:content/metadata/CAMPAIGNNAME] IS NOT NULL order by [jcr:content/metadata/CAMPAIGNNAME] ASC



Arun Patidar

View solution in original post

4 Replies

Avatar

Community Advisor

Hello @FarzanaA 

 

The issue might be with the properties that are used. Please cross-check that they are part of the index for dam:Asset. Example OWNEDBY and CAMPAIGNNAME


Aanchal Sikka

Avatar

Level 2

Hi Aanchal,

 

Thanks for the quick reply. We will definitely try this out. And if any query will let you know.

 

Thanks once again.

Avatar

Correct answer by
Community Advisor

Try to improve query with exact node name or path

e.g.

select * from [dam:Asset] where ISDESCENDANTNODE([/content/dam/all-campaigns]) AND [jcr:content/metadata/OWNEDBY]='{name}' and [jcr:content/metadata/CAMPAIGNNAME] IS NOT NULL order by [jcr:content/metadata/CAMPAIGNNAME] ASC



Arun Patidar

Avatar

Level 2

Hi Arun,

 

Thank for your help it worked fine.

 

Thanks & Regards

Farzana.