Clarity Development III (Queries, NSQL)

--- Queries  in Clarity---


NSQL
SELECT field
FROM table *

-How to find the names to use from Clarity.

Table=Table name. Check in Object, Attributes, "Database Table" column
* i.e for custom objects: odf_ca_cop_xxxxxx

Field=Attribute name.
For custom attributes, check in Object, Attributes, "Database Column" column
For default attributes, click on "Attribute" column, and use  "Attribute ID" (in Object Attribute window)
For virtual attributes, check in Object, Attributes, "Database Column" column

-NSQL Query in a Lookup-Query:
Lookup-query supports works with a quite standar SQL syntaxis

i.e
SELECT   @SELECT:my.code:code@,       
         @SELECT:P.PROJECT_CODE:PROJECT_CODE@,       
FROM   odf_ca_myobject my, PAC_MNT_PROJECTS P
WHERE @FILTER@


-NSQL query in QUERY 

Requires the comple NQL syntaxis and at least one dimension definition (SELECT:DIM:)

i.e.
SELECT   @SELECT:DIM:USER_DEF:IMPLIED:Mytest:my.code:code@,       
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:Mytest:my.created_by:created_by@
FROM   odf_ca_myobject my
WHERE  @FILTER@

-Table Prefixes


Prefix,  Table Topic
BIZ,Opportunities
CAL,Events
CAP,Capacity Planning
CLB,Collaboration
CMN,Common tables that span functionality
CMN_RPT,Reporting
CMN_SEC,Security
MR,Methodology
NBI,Datamart
NTD,Discussions
OBS,Organizational Breakdown Structure
PAC,Project Accounting
PPA,Project Billing
PR and PRJ,Projects
RSM,Resource management
SRM,Main objects table that crosses functionality modules
TAX,Tax
XDM,eXtensible Data Model (XDM) for user-defined fields and forms

(added tables)
COP, from PMO Accelerator (not tied to any specific industry)
CATS,  from Best Practises Accelerator (old) (BPA)
ODF,
ODMD, CA Clarity PPM Essentials
PR2, Accelerator PRINCE2 (tied to PRINCE2 from Office of Government Commerce)
PBK, Accelerator PMBOK guide (tied to PMI PMBOK)
CSK, Accelerator Program Management Office (PMO)

TIPS for Naming in Clarity (objects, attributes,...)
qry query
q_ query
lkp lookup
l_ lookup
prt portlet
rpt report
prj project
proc process
res resource
tab tab
obj object
o_ object
pfl portafolio