Clarity

 View Only
Expand all | Collapse all

Multi-select on an NSQL Parameter?

  • 1.  Multi-select on an NSQL Parameter?

    Posted May 05, 2010 12:33 PM
    Hi all. I think the answer is "no, you can't do that" but I thought I'd ask.  I have a parameter that's single select for Resource:   (r.user_id = @where:param:user_def:integer:user_id@ or @where:param:user_def:integer:user_id@ is NULL) We now want this to be multiselect. Although the NSQL tasks the IN change   (t1.user_id IN (@where:param:user_def:integer:userid@) or @where:param:user_def:integer:userid@ is NULL)   The control in the portlet doesn't present me with the mulit-select checkbox.  I think the answer here is "refactor the code and make Resource an Attribute" but I just wanted to check to see if I'm doing something wrong.


  • 2.  Re: Multi-select on an NSQL Parameter?
    Best Answer

    Posted May 07, 2010 12:05 AM
    No you can't do that.  --  The trouble is that its a "where:param" attribute, and you can't implement multiu-selects for them.   If you work out how the NSQL to SQL compiler is working (just "Preview" the query) then you see that it just implements these fields as a "=" whereas you would need it to implement as a "IN" to get any sort of multi-select working.   If you look how a "normal" attribite gets processed when it is a multi-select, you can even see that Clarity changes the SQL "on the fly" to build up the "IN" clause dynamically (which is a performance concern for SQL-techies!).  The answer to your "functional" question is then either what you have realised (refactor and make the field a "normal" attribute (that might not be possible?)) OR you could implement multiple "where:param" fields and code the "IN" (or "OR") logic yourself in the query.   (This latter is really UGLY functionality, but it will work)


  • 3.  Re: Multi-select on an NSQL Parameter?

    Posted May 07, 2010 10:05 AM
      |   view attached
    Thanks for the response Dave - I never thougt of clicking Preview to see what the compiler thought of my change.    I've attached a screen shot - it appears to be  taking the IN (see attached).  I xogged out the portlet and changed the multiValued to "true" and xogged it back in:     Good News! Now I have an NSQL that says it accepts mulitiple values in a parameter  and a portlet that  allows me to select  multiple values to send to the parameter.  Guess what? It doesn't work.  That's enough weird science for now - I think I'm sticking with the answer "Sorry, that's a nice to have enhancement that unfortunately the tool doesn't support at this time..."  Thanks!


  • 4.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Mar 22, 2011 12:25 PM
    @Rob : bump?


  • 5.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Mar 22, 2011 12:37 PM
    I'm busy juggling some other things right now, but if you have some cycles to let this genie out of the bottle - pull the cork! It's easier to ask forgiveness than permission, right?

    Post up some samples - we'll ask forgiveness later! :tongue

    Undocumented - buyer beware, your mileage may vary, all the other disclaimers and caveats should apply. :wacko:


  • 6.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Mar 22, 2011 02:15 PM
      |   view attached
    Hi all. Give this syntax a go -
    @WHERE:PARAM:[Data Type]:[Table.Field]:[Alias]@
    Somethin' like this...
    SELECT 
    @select:dim:user_def:implied:issue:I.ID:int_id@
    , @select:dim_prop:user_def:implied:issue:I.NAME:issue_name@
    , @select:dim_prop:user_def:implied:issue:(select top 1 NAME from cmn_lookups_v where language_code = 'en' and lookup_type = 'RIM_CATEGORY_TYPE' and LOOKUP_CODE = I.CATEGORY_TYPE_CODE):category_name@
    , @select:dim_prop:user_def:implied:issue:(select top 1 NAME from cmn_lookups_v where language_code = 'en' and lookup_type = 'RIM_PRIORITY' and LOOKUP_CODE = I.PRIORITY_CODE):priority_name@ 
    , @select:dim_prop:user_def:implied:issue:(select top 1 NAME from cmn_lookups_v where language_code = 'en' and lookup_type = 'RIM_STATUS' and LOOKUP_CODE = I.STATUS_CODE):status_name@
    , @select:dim_prop:user_def:implied:issue:AT.FULL_NAME:assigned_to_name@
    , @select:dim_prop:user_def:implied:issue:INV.NAME:project_name@
    , @select:dim_prop:user_def:implied:issue:PM.FULL_NAME:pm_name@
    
    FROM RIM_RISKS_AND_ISSUES I 
    JOIN INV_INVESTMENTS INV ON I.PK_ID = INV.ID
    JOIN SRM_RESOURCES AT ON I.ASSIGNED_TO = AT.USER_ID
    JOIN SRM_RESOURCES PM ON INV.MANAGER_ID = PM.USER_ID
    WHERE I.TABLE_NAME = 'SRM_PROJECTS'
    AND I.TYPE_CODE = 'ISSUE'
    AND ((I.ASSIGNED_TO IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:I.ASSIGNED_TO:ASSIGNED_TO@))
    AND ((INV.MANAGER_ID IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:INV.MANAGER_ID:PM_ID@))
    AND ((INV.ID IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:INV.ID:PROJECT_ID@))
    AND ((I.STATUS_CODE IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:I.STATUS_CODE:STATUS_CODE@))
    AND ((I.CATEGORY_TYPE_CODE IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:I.CATEGORY_TYPE_CODE:CATEGORY_CODE@))
    AND ((I.PRIORITY_CODE IS NULL) OR (@WHERE:PARAM:USER_DEF:STRING:I.PRIORITY_CODE:PRIORITY_CODE@))
    AND EXISTS (select 1 FROM prj_obs_associations assoc 
          LEFT OUTER JOIN prj_obs_units_flat flat ON assoc.unit_id = flat.unit_id 
          AND assoc.table_name = 'SRM_PROJECTS' 
          WHERE ((inv.id = assoc.record_id) OR @WHERE:PARAM:USER_DEF:INTEGER:PROJECT_OBS@ IS NULL) 
          AND flat.branch_unit_id = @NVL@(@WHERE:PARAM:USER_DEF:INTEGER:PROJECT_OBS@,flat.branch_unit_id)) 
    AND EXISTS (select 1 FROM prj_obs_associations assoc 
          LEFT OUTER JOIN prj_obs_units_flat flat ON assoc.unit_id = flat.unit_id 
          AND assoc.table_name = 'SRM_RESOURCES' 
          WHERE ((at.id = assoc.record_id) OR @WHERE:PARAM:USER_DEF:INTEGER:RESOURCE_OBS@ IS NULL) 
          AND flat.branch_unit_id = @NVL@(@WHERE:PARAM:USER_DEF:INTEGER:RESOURCE_OBS@,flat.branch_unit_id))
    AND      @FILTER@
    ... will let you do something like this. Multiselect parameters in an NSQL portlet.
    .


  • 7.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Aug 14, 2012 04:34 PM
      |   view attached
    If you attempt to use a multi-select parameter in an NSQL with unions (where the parameter will appear multiple times), the following error is thrown:

    NPT-0105: Parameter 'param_project_id' is inconsitently specified.

    We knew there were limitations somewhere on this...

    .


  • 8.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Aug 14, 2012 04:50 PM

    Rob.Ensinger wrote:

    NPT-0105: Parameter 'param_project_id' is inconsitently specified.
    (Just GUESSing here Rob)

    Are the mulltiple references to the parameter (in the different UNION bits) all consistantly aligned to the same underlying "column" or to different columns (or even alias's)? And if the latter, does that help at all if you can reconstruct the SQL so that they ARE consistant?

    --

    What I mean is
    blah blah
    WHERE ( @WHERE:PARAM:USER_DEF:INTEGER:[color=#ff0000]INV.ID[/color]:PROJECT_ID@ )
    blah blah
    UNION
    blah blah
    WHERE ( @WHERE:PARAM:USER_DEF:INTEGER:[color=#ff0000]INV.ID[/color]:PROJECT_ID@ )
    blah blah
    UNION
    blah blah
    WHERE ( @WHERE:PARAM:USER_DEF:INTEGER:[color=#ff0000]INV.ID[/color]:PROJECT_ID@ )
    blah blah
    (consistantly aligned)
    blah blah
    WHERE ( @WHERE:PARAM:USER_DEF:INTEGER:[color=#ff0000]INV.ID[/color]:PROJECT_ID@ )
    blah blah
    UNION
    blah blah
    WHERE ( @WHERE:PARAM:USER_DEF:INTEGER:[color=#ff0000]PRTASK.PRPROJECTID[/color]:PROJECT_ID@ )
    blah blah
    UNION
    blah blah
    WHERE ( @WHERE:PARAM:USER_DEF:INTEGER:[color=#ff0000]INV2[/color].ID:PROJECT_ID@ )
    blah blah
    (different)

    As I say just a GUESS based on that "inconsistanly specified" bit of the error message?


  • 9.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Aug 14, 2012 05:09 PM
    Hi Dave - thanks for putting the thinkin' cap on for this. I tried this also (pared down a little test case and unioned the exact same query), but still no go. My hunch is the NSQL validator gets scared when it sees this - we are colouring outside the lines here a bit so I'm not surprised.

    I have opened an Idea to have this properly (fully?) supported and documented. Please click and promote[b][size=5] Multi-Select Paramters in NSQL[size] if you believe this is something that would provide value to your organization.


  • 10.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Aug 14, 2012 05:46 PM
    I just looked back at that conversation we had >12 months ago around this issue, and that "inconsistanly specified" error was what you were getting back then too wasn't it (I forgot!).

    The result of that conversation was just me suggesting that we could move the parameter "outside" of the union'd statement rather than using it inside each statement... but of course, functionally that just might not work for you :sad

    Moving the union'd SQL logic into a database VIEW is the same sort of soilution too I think; would work technically but might not hit the functional buttons you need it to...


  • 11.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Mar 03, 2014 01:41 PM
    Close to 4 years after the original conversation I just had a problem to solve that could only be done with this mulit-select parameter trick. I came to the forums to get my head back around what we worked out in 2010. As before this is still not as trouble free as the standard single select parameter synatax, but with a little work you can be useful. In this case I'm sending in a range of values - pretty cool.
     
    Dave - I totally agree with your statement above. This is kind of wonky and we'll probably never get to root cause on why this doesn't just work all the time (I observe Unions and Sub-Selects don't work, there's probably more out there), but by reworking the structure of your query and even using views to pre-join, this can be a powerful tool in the toolbox.
     
    'Thank You' to the CA Forums for facilitating collaboration & persiting this knowledge, allowing me to look this up 4 years later to solve today's challanges.
     
     


  • 12.  Re: RE: Re: Multi-select on an NSQL Parameter?

    Posted Apr 28, 2015 02:47 PM

    Hi Rob,

     

    Did you have the solution of this last answer? I can not see the picture.

     

    Best Regards,



  • 13.  RE: Re: Multi-select on an NSQL Parameter?

    Posted Jan 07, 2021 08:03 AM
    Edited by Pawel Krystosiak Jan 07, 2021 08:04 AM
    Hi Rob,

    This should be marked as the "Best Answer" (your post from 03-22-2011, I mean). I got it to work, so I am a living proof that it is the right way of getting the Multi-Value Lookups in the Filter in your NSQL query!

    And, by the way, I have also seen the "NPT-0105: Parameter 'param_xxx' is inconsistently specified." error message.
    But in my case, it was rather simple to fix it. I had to replace:

    WHERE (@WHERE:PARAM:USER_DEF:STRING:R.STATUS_CODE:RISK_STATUS@ IS NULL OR
        @WHERE:PARAM:USER_DEF:STRING:R.STATUS_CODE:RISK_STATUS@)

    with

    WHERE (R.STATUS_CODE IS NULL) OR
        @WHERE:PARAM:USER_DEF:STRING:R.STATUS_CODE:RISK_STATUS@)

    ​​Regards,
    Pawel


  • 14.  Re: Multi-select on an NSQL Parameter?

    Posted Apr 25, 2019 03:53 AM

    For information, https://docops.ca.com/ca-ppm/15-5/en/reference/ca-ppm-studio-development/ca-ppm-studio-nsql-queries#CAPPMStudioNSQLQueries-TheSTRING_LISTNSQLConstruct

     

    Even if you try to XOGout and XOGin portlet with property multiValued="true" on Filter Attribute (to force multi-select), the filter does not work

     

    Regards,

    David