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
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How do I limit the number of results from a SQL2 query?

TomMaton
Level 1
Level 1

I'm running a query in the CQ5.5 query tool and trying to return a just 20 results.

My query is like this:
SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/clientdomain/en]) and ( contains('jcr:description', 'lorem') or contains('jcr:title', 'lorem') ) LIMIT 10

But I keep on getting the following error
Query: SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/clientdomain/en]) and ( contains('jcr:description', 'lorem') or contains('jcr:title', 'lorem') ) LIMIT(*)10; expected

Thanks in advance,

Tom

1 Accepted Solution
smacdonald2008
Correct answer by
Level 10
Level 10

Using Limit in the Query syntax does seem to throw the exception that you have pointed out. A better way to set the limit is to call setLimit on the query object as shown here (See the bold code):

//Create a connection to the CQ repository running on local host 
    Repository repository = JcrUtils.getRepository("http://localhost:4502/crx/server");
   
   //Create a Session
   javax.jcr.Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray())); 
 
  
//Obtain the query manager for the session ...
  javax.jcr.query.QueryManager queryManager = session.getWorkspace().getQueryManager();
    
//Setup the quesry based on user input     
  String sqlStatement="";
       
  
 //   sqlStatement = "SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/clientdomain/en]) and ( contains('jcr:description', 'lorem') or contains('jcr:title', 'lorem') ) LIMIT 10";
  
  sqlStatement = "SELECT * FROM [nt:unstructured] WHERE CONTAINS(desc, 'Customer')" ; 
           
  javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");
  
  query.setLimit(4); // set the limit
   
  //Execute the query and get the results ...
  javax.jcr.query.QueryResult result = query.execute();
   
  //Iterate over the nodes in the results ...
  javax.jcr.NodeIterator nodeIter = result.getNodes();
  
  int count = 0; 
  while ( nodeIter.hasNext() ) {
      
      count++; 
      nodeIter.nextNode();
  }
  
  System.out.println("THere are this many records "+count); 

*****************************************************************************************

This code returns 4 as set by the code. 

Hope this helps! 

View solution in original post

2 Replies
smacdonald2008
Correct answer by
Level 10
Level 10

Using Limit in the Query syntax does seem to throw the exception that you have pointed out. A better way to set the limit is to call setLimit on the query object as shown here (See the bold code):

//Create a connection to the CQ repository running on local host 
    Repository repository = JcrUtils.getRepository("http://localhost:4502/crx/server");
   
   //Create a Session
   javax.jcr.Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray())); 
 
  
//Obtain the query manager for the session ...
  javax.jcr.query.QueryManager queryManager = session.getWorkspace().getQueryManager();
    
//Setup the quesry based on user input     
  String sqlStatement="";
       
  
 //   sqlStatement = "SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/clientdomain/en]) and ( contains('jcr:description', 'lorem') or contains('jcr:title', 'lorem') ) LIMIT 10";
  
  sqlStatement = "SELECT * FROM [nt:unstructured] WHERE CONTAINS(desc, 'Customer')" ; 
           
  javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");
  
  query.setLimit(4); // set the limit
   
  //Execute the query and get the results ...
  javax.jcr.query.QueryResult result = query.execute();
   
  //Iterate over the nodes in the results ...
  javax.jcr.NodeIterator nodeIter = result.getNodes();
  
  int count = 0; 
  while ( nodeIter.hasNext() ) {
      
      count++; 
      nodeIter.nextNode();
  }
  
  System.out.println("THere are this many records "+count); 

*****************************************************************************************

This code returns 4 as set by the code. 

Hope this helps! 

View solution in original post