AVG: Return only the average value from all that qualify:
SELECT AVG("b" + "c") FROM "abc" WHERE "a" = '10'
COUNT: Return a count of a specific type objects in the repository owned by a particular user:
SELECT COUNT(*) FROM type WHERE owner_name = 'tyler'
COUNT(DISTINCT): Return a count of unique title values contained in all specific typed objects:
SELECT COUNT(DISTINCT title) FROM type
DISTINCT: Return only unique rows of information, suppressing duplicate rows:
SELECT DISTINCT owner_name, a_content_type FROM type
ENABLE: Apply a DQL hint to a query:
SELECT r_object_id, object_name, owner_name FROM type ENABLE (OPTIMIZE_TOP 5)
ESCAPE: Define an escape character and use it to evaluate escaped characters literally, in this
example, the underbar (_) is escaped so it will not be evaluated as a wildcard:
SELECT r_object_id FROM type WHERE object_name LIKE '%_%' ESCAPE ''
EXECUTE: Run administration method. Each method has unique arguments:
EXECUTE PURGE_AUDIT WITH DELETE_MODE='DATE_RANGE', date_start='01/01/2000 00:00:00 AM', date_end='01/01/2004 00:00:00 AM'
FOLDER: Return objects that are only contained in specific cabinet/folder locations;
SELECT object_name, r_creation_date FROM type WHERE FOLDER('/S', DESCEND) OR FOLDER('/S/D')
GRANT: Grant the indicated privilege to users selected from a subquery:
GRANT create cabinet TO (SELECT user_name FROM dm_user WHERE r_modify_date < DATE('05/01/2006'))
GROUP BY: Return a count of documents, grouping the results by owner and lock owner:
SELECT owner_name, r_lock_owner, count(*) FROM type GROUP BY owner_name, r_lock_owner
HAVING: Return a count of documents owned by users, but only if they own fewer than 10 documents:
SELECT owner_name, count(*) FROM dm_document GROUP BY owner_name HAVING count(*) < 10
LIKE: Return objects where the specified conditional string appears anywhere in a particular attribute:
SELECT object_name, subject, title, authors FROM type WHERE subject LIKE 'c%'
LIKE and UPPER: Return objects where the specified conditional string, regardless of case, appears anywhere in a particular attribute:
SELECT object_name, subject, title, authors FROM type WHERE UPPER(subject) LIKE '%Region%'
MAKE_INDEX: Create a unique index for dm_sysobject for the r_creator_name and r_creation_date properties:
EXECUTE MAKE_INDEX WITH type_name='dm_sysobject', attribute='r_creator_name', attribute='r_creation_date', unique=TRUE
MAX: Return just the maximum value from all that qualify:
SELECT MAX("b" + "c") FROM "type" WHERE "a" = '10'
MFILE_URL: Return URLs to the PDF renditions of the first content pages of documents:
SELECT object_name, owner_name, MFILE_URL ('pdf', 0,) FROM type
ORDER BY: Sort the results first by owner_name in descending order and then by r_creation_Date in ascending order:
SELECT owner_name, object_name, r_creation_date FROM type ORDER BY owner_name DESC, r_creation_date ASC
ORDER BY: Sort the results first by second selected property:
SELECT owner_name, object_name, r_creation_date FROM type ORDER BY 2
REVOKE: Revoke the indicated privilege from specified users:
REVOKE create cabinet, create type FROM barry, carolyn
SUBSTR: Returns documents where the title attribute begins with the letters 'SOP'. In this example, the SUBSTR function isolates just the first three characters of the title attribute, allowing the query to ignore any characters that might follow.
SELECT object_name, authors FROM type WHERE SUBSTR(title,1,3) = 'SOP'
SUM: Return the sum of all values that qualify:
SELECT SUM("b" + "c") FROM "abc" WHERE "a" = USER
TYPE: Return objects only from specific subtypes:
SELECT object_name, owner_name, r_creation_date FROM dm_sysobject WHERE TYPE(dm_note) or TYPE(dm_script)
UNION: Return results from two combined result sets:
SELECT "name", "address", "ss_num" FROM "current_employees" UNION SELECT "ret_name", "ret_address", "ret_ss_num" FROM "retired_employees"