Clarity

Hi,

Somebody can tell me How to add an attribute of an object to a query in an nsql

Thanks a lot

Cira

See the default NSQL in a new query how attributes are defined.

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:RSRC@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:MANAGER@,
         @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):PROJECT_COUNT:AGG@
FROM     INV_INVESTMENTS I,
         SRM_RESOURCES R,
         SRM_RESOURCES MR,
         CMN_SEC_USERS U
WHERE    I.CREATED_BY = U.ID
AND      U.ID = R.USER_ID
AND      R.MANAGER_ID = MR.USER_ID
AND      @FILTER@
GROUP BY R.FULL_NAME,
         MR.FULL_NAME
HAVING   @HAVING_FILTER@

If you want to add an attribute to an existing query with the least hassle add it after the last existing atribute.

Thank you so much,

Cira

Hi Cira,

Please check NSQL manual.

https://docops.ca.com/ca-ppm/15-4-1/en/reference/ca-ppm-studio-development/ca-ppm-studio-nsql-queries/ 

Above manual says below.

Defining the Dimension Properties Column

Once the dimension is defined, all other fields are referred to as dimension properties. The syntax that you use to define the dimension column is the same as the one you use to define the dimension properties column(s) with one exception. Instead of using DIM after the select statement, use DIM_PROP.

SELECT @SELECT:DIM:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@      @SELECT:DIM_PROP:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@

When defining the Dimension Properties column:

• Each statement begins and ends with the @ character.
• Use IMPLIED if the data type does not need to be further quantified (than what can be derived from the database).
• <Dimension> must be the same Dimension name as for the Dimension column
• <Table.Field> is the Table or Alias name and field from CA PPM.

• <label> is the user-defined name or the field that appears in the query.

  SELECT     @SELECT:DIM_PROP:USER_DEF:IMPLIED:<Dimension>:<Table.Field>:<label>@

Using the sample dimension statement from above, we add the names of the resource and the Manager dimension properties, resulting in the following example:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.ID:RSRC_ID@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:RSRC@, @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:MANAGER@

In the preceding statement, the unique name of the dimension is the same for the three columns. This convention tells the NSQL engine that the three columns belong together.

Regards,
Shoichi