SQL2 JCR query issue with AEM 6

Question

I have the following query, which produces no results, but no error. I am trying to select all pages that are not "featured press releases" and are of a certain type, e.g. "Announcements".

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] 
AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent,     '/content/acme/en_US/site-content/content-modules/resources/press-releases')  
AND (NOT CONTAINS(child.[cq:tags], 'acme-    www:FeaturedResources/FeaturedPressRelease*')) 
AND (CONTAINS(child.[cq:tags], 'acme-www:PressReleaseType/Announcement')) 
AND child.[pressReleaseDate] > CAST('2015-01-01T00:00:00.000Z'  AS DATE)  
AND child.[pressReleaseDate] < CAST('2016-01-01T00:00:00.000Z'  AS DATE)  
order by child.[jcr:title]

If I remove the line "AND (NOT CONTAINS ..." I get correct results. Also, if I put that line back, and remove the line "AND (CONTAINS( ..." I get correct results. But having both lines in there at the same time gives me no results. There are pages that should be qualified, i.e. they are not featured press releases and they are of type announcement, so it seems to me that logically this should work.

Does anyone know of any reason why the query would not work? I am new to SQL2, and I cannot find any similar examples on the web, just descriptions of syntax in general terms.

Thanks!

Cris Rockwell Cris Rockwell · Accepted Answer · 2015-12-10T23:12:03

I'm not sure if the splat in one of your tags was supposed to be a wildcard, but if so that might use the LIKE % syntax. cq:tags is a String[], but I haven't had luck using contains like that... hopefully others can say why. you can try something like this...

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base]
AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent,     '/content/acme/en_US/site-content/content-modules/resources/press-releases')
AND NOT (child.[cq:tags] = 'acme-www:FeaturedResources/FeaturedPressRelease')
AND child.[cq:tags] = 'acme-www:PressReleaseType/Announcement'
AND child.[pressReleaseDate] > CAST('2015-01-01T00:00:00.000Z'  AS DATE)
AND child.[pressReleaseDate] < CAST('2016-01-01T00:00:00.000Z'  AS DATE)
order by child.[jcr:title]

SQL2 JCR query issue with AEM 6

1 Answers