Relational query syntax introduction

The SQL statement for a query begins with SELECT This, followed by the SQL commands for column definitions and search criteria. If the SQL statement includes a JOIN clause, the reference to This must be fully qualified with a table name, such as CustomObject.This.

Here are some examples of different types of syntax:

A simple query 

SELECT This, ContentSize, Creator, Id  
FROM Document  
WHERE (ContentSize > 0 OR Creator = 'Administrator')
A table join (to find unfiled custom objects) 

SELECT CustomObject.This, CustomObject.Name  
FROM CustomObject  
LEFT JOIN ReferentialContainmentRelationship rcr ON Cust
Date comparisons 

SELECT This, Creator, DateCreated, CurrentState  
FROM Document  
WHERE (DateCreated > 20010401 T000000Z AND Creator = 'Administrator')
Custom search functions
In the following example, the custom search function is CmRpt::FormatDate. For information about search functions, see Creating custom search functions.

SELECT This, CmRpt::FormatDate(DateCreated, 'yyyy') 
FROM Document
In the following example, the custom search function is NewRpt::UserRecord. This function implements a Java™ code or Java script handler to populate columns in a dimension table. It is an example of finer aggregation because it groups by three different entities. The disadvantage of such a search is that it takes longer to run and populates a lot more rows. But it has the advantage of allowing for greater drill-down as well as slicing and dicing capabilities in OLAP reporting tools such as IBM® Cognos® Business Intelligence.

SELECT NewRpt::UserRecord(Creator) AS CmRptUserRecord,
NewRpt::ClassRecord(ClassDescription) AS NewRptClassRecord,
NewRpt::DateRecord(DateCreated) AS NewRptDateRecord,
COUNT(Id) AS CmRptObjectCount,
SUM(ContentSize) AS CmRptContentSize
FROM Document
GROUP BY CmRptUserRecord, NewRptClassRecord, NewRptDateRecord
Full-text search (CBR queries)

Full-text searches use the CONTAINS function in CBR queries to search indexed content. Content Engine supports Content Search Services (CSS) and Elasticsearch/OpenSearch as search engines. When dual mode indexing is configured, you can specify which search engine to use. For detailed information about CBR query syntax, search engine options, and dual mode indexing, see CBR query syntax introduction.
Virtual table
A virtual table expression can be used in the FROM clause in place of a class name. The virtual table expression has the form:
namespace::name(<underlying class> [FIRST], optional parameters)
The virtual table handler which implements the virtual table defines the namespace and name of the virtual table.

The optional FIRST keyword determines how the query is executed, similar to how the CBR_DB_FIRST and CBR_CONTENT_FIRST options affect CBR queries.

You can include zero or more parameters separated by commas. Each parameter must be a literal constant and must be in the standard format accepted in Content Engine SQL. The number and the datatype of the parameters can vary between virtual tables.

The following is an example of the vector search virtual table that is defined by the GenAI Extensions add-on:
SELECT … FROM GenAI::VectorSearch(<document class>, ‘prompt’ [, <relevancy filter level>]) WHERE <property conditions>