Clarity

  • 1.  NSQL

    Posted Jun 27, 2018 11:45 PM

    Hello All

     

    I am very new to NSQL. Do you know any tutorial/ manual to learn the basic popular NSQL Syntax?

     

    Regards

    Parastoo



  • 2.  Re: NSQL

    Broadcom Employee
    Posted Jun 28, 2018 12:49 AM

    Hi ParastooMousavi82365653,

     

    There is manual for nsql.

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

     

    Regards,

    Shoichi



  • 3.  Re: NSQL

    Posted Jun 28, 2018 02:18 AM

    That is the "Grammar of the NSQL"

    If you wanna learn to create your own NSQL you might also consider

    1. Create a new query and a portlet based on that. When you create a new NSQL query it is populated by default with a simple query.

    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@

    2. Once you get that working modify that to something else, eg. other fields or other tables for other objects.

    3. Look at the queries that exist in the system.

    4. Consider putting your SQL query inside a wrapper to simplify the NSQL

    that is

    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:Mysql:UNIQUEID@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:Mysql:Field1:Field1@,
             @SELECT:DIM_PROP:USER_DEF:IMPLIED:Mysql:Field2:Field2@,

    and so on
    FROM  (This is where you have your fancy SQL which could choke a horse, but eventually returns a number of fields and values)

    WHERE       @FILTER@

     

    Try to not to use reserved words like start, finish, id etc.

    5. If there are errors when you preview try to interpret them to correct the NSQL

    6. Ask for help in this community (after you have searched  for similar questions.



  • 4.  Re: NSQL

    Posted Jun 28, 2018 04:07 AM

    This is a helpful site ; http://www.gelscripting.com/#nsql



  • 5.  Re: NSQL

    Posted Jun 28, 2018 06:57 AM

    Right, there you can practice.