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:

Function	What It Is
<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;