dqMan Proprietary Statements
The proprietary statements provided by dqMan simplify the user's tasks by employing a language that closely resembles SQL.
Distinguished by the dqMan logo, these proprietary statements are visually marked on the corresponding query row.
dqMan empowers users by enabling them to utilize select *, group by, order by, count, as well as descriptions of various fields or relations.
Select statement extensions
Selecting all queryable fields
SELECT * FROM user__sys;
SELECT * FROM group__sys;
SELECT * FROM binder_node__sys;
The SELECT * statement is used to retrieve all queryable fields from a specified target. In the given examples, SELECT * is used to fetch all queryable fields from the targets "user__sys," "group__sys," and "binder_node__sys."
Counting the objects in a query:
SELECT count(*) FROM documents WHERE type__v = 'Clinical'
The COUNT(*) function is used to calculate the number of rows returned by a query. In the provided example, the COUNT(*) function is used to count the records in the "documents" where the "type__v" column is equal to 'Clinical.'
Caching the query results in a local table:
SELECT
id,
major_version_number__v,
minor_version_number__v,
name__v,
title__v,
document_number__v,
type__v,
subtype__v,
classification__v,
status__v,
document_creation_date__v,
version_creation_date__v,
version_modified_date__v,
created_by__v,
version_created_by__v,
last_modified_by__v,
lifecycle__v,
state_stage_id__sys,
global_id__sys,
filename__v ,
clinical_study__v,
product__v
FROM
documents
WHERE
type__v = 'Clinical'
INTO local_clinical;
The INTO clause is used to store the results of a query into a local table. In the given example, the query results from selecting specific columns from the "documents" table, where the "type__v" column is equal to 'Clinical,' are stored in a local table named "local_clinical."
Querying local tables by using the full SQLite syntax
SELECT
subtype__v,
classification__v,
count(id) as number_of_documents
FROM
local_clinical
GROUP BY subtype__v, classification__v;
After storing query results in a local table, regular SQL syntax, specifically SQLite syntax in this case, can be used to query the local table. The provided example demonstrates a SELECT statement on the "local_clinical" table, where the results are grouped by "subtype__v" and "classification__v," while also counting the number of documents using the COUNT function.
The full syntax for querying the local tables can be found here: https://www.sqlite.org/lang_select.html
DESCRIBE command
Syntax:
Describe <type> [fields|relations] [all|custom]
Describe <[\]doc_type[\doc_subtype[\doc_classification]]> [all|custom]
Arguments:
<type>
Object name, such as product__v
, staged__v
or document type, subtype and classification
[all|custom]
all
lists all attributes including all inherited ones. custom
lists all customized attributes, including parent objects. If no argument is supplied, only the type related attributes will be listed.
(Available for object and document type only)
Examples:
Describing object and document relations:
The DESCRIBE statement is used to obtain information about relations of an object in the system. In the given examples, the DESCRIBE statement is used to retrieve information about the relations of objects such as "activity__v" and "user__sys."
describe <object> relations - returns all relations of an object
describe <document type> relations - returns all relations of the document type
DESCRIBE activity__v RELATIONS;
DESCRIBE user__sys RELATIONS;
Describing document and object fields:
To view all fields of a document or object type in the results grid, users can utilize the "describe" command. The syntax for the "describe" command is as follows:
DESCRIBE object | []doc_type [\doc_subtype [\doc_classification]] [[[ALL|CUSTOM] FIELDS]]
describe <object> fields - returns all fields of an object
describe <document type> fields - returns all fields of the document type
describe <document type> custom fields - returns only the fields defined for the specified document type hierachy
<document type> can be any type hierarchy : type\subtype\classification. Subtype and classification are optional
In the provided example, the DESCRIBE statement is used to retrieve information about the fields of the "\regulatory__c" document type, including both standard and custom fields.
DESCRIBE \regulatory__c FIELDS;
DESCRIBE \regulatory__c CUSTOM FIELDS;
Last updated
Was this helpful?