Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Sql2 query to find pages containing duplicate property value

Avatar

Avatar
Validate 1
Level 2
skmAem
Level 2

Likes

14 likes

Total Posts

34 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Ignite 1
Give Back
Boost 5
Boost 3
View profile

Avatar
Validate 1
Level 2
skmAem
Level 2

Likes

14 likes

Total Posts

34 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Ignite 1
Give Back
Boost 5
Boost 3
View profile
skmAem
Level 2

24-07-2020

Hi AEM Gurus,

 

Is it possible to use jcr sql2 query to find pages that contain the same id value (supposed to be unique but got duplicated some how) in one of the custom page property? I have tried with this sql2 query but it fails since we can't compare a dynamic operand against a dynamic operand. Since sql2 doesn't have group by, having clauses, my choices seem to be limited. I also tried playing around with not issamenode but no luck so far.

 

select n1.[jcr:path], n2.[jcr:path] from [cq:PageContent] as n1 INNER JOIN [cq:PageContent] as n2
ON n1.[custom_id] = n2.[custom_id]
where ISDESCENDANTNODE(n1, "/content") and ISDESCENDANTNODE(n2, "/content")
and n1.[jcr:path] <> n2.[jcr:path]

 

So basically custom_id page property should have been unique across repository but with users copy/pasting pages, same custom_id value can be on multiple pages and we need to find these pages.

 

Thanks in advance,

SKM

 

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Give Back
Level 2
narendragandhi
Level 2

Likes

17 likes

Total Posts

14 posts

Correct Reply

6 solutions
Top badges earned
Give Back
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Give Back
Level 2
narendragandhi
Level 2

Likes

17 likes

Total Posts

14 posts

Correct Reply

6 solutions
Top badges earned
Give Back
Boost 5
Boost 3
Boost 10
Boost 1
View profile
narendragandhi
Level 2

31-08-2020

Hi, It may not exactly be the solution you are looking for but you could run a query to get all page path and custom_id and then run export the result into an excel and find out the duplicates. You can use http://localhost:4502/editor.html/var/acs-commons/reports/custom.html to run queries and download results.

 

Screen Shot 2020-08-07 at 3.48.24 PM.png

Answers (1)

Answers (1)

Avatar

Avatar
Validate 1
Level 2
skmAem
Level 2

Likes

14 likes

Total Posts

34 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Ignite 1
Give Back
Boost 5
Boost 3
View profile

Avatar
Validate 1
Level 2
skmAem
Level 2

Likes

14 likes

Total Posts

34 posts

Correct Reply

1 solution
Top badges earned
Validate 1
Ignite 1
Give Back
Boost 5
Boost 3
View profile
skmAem
Level 2

07-08-2020

Asking it again to see if anyone has pointers...