Clarity

Expand all | Collapse all

Clarity NSQL Lookup attributes

  • 1.  Clarity NSQL Lookup attributes

    Posted Sep 04, 2012 09:17 AM
    Hi,

    We're using Clarity v12 and I have a question.
    We have a portlet implemented whose query i need to modify a little bit.
    Existing query looks like :
    [center]@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:inv.stage_code :PROJECT_PHASE@ ,
    when 'CMS_Stage0110' then 'Origination'
    when 'CMS_Stage0101' then 'Initiation'
    when 'CMS_Stage0120' then 'Planning'
    when 'CMS_Stage0130' then 'Execution and Control'
    when 'CMS_Stage0140' then 'Closing' end :PROJECT_PHASE@ , Close[center]

    Now i need to remove the case and have the values in a lookup so that it can be extended easily in the future.
    Can anyone guide me as to how to accomplish this?


    Thanks !!


  • 2.  RE: Clarity NSQL Lookup attributes

    Posted Sep 04, 2012 09:41 AM
    Just need a little bit of logic in your portlet query something like this;
    SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MY_LOOKUP_ID' and language_code = @WHERE:PARAM:LANGUAGE@
    The @WHERE:PARAM:LANGUAGE@ is to pick up the correct language from the lookups data according to your logged on user.

    --

    Note that there is a stock "Project Stage" lookup "INV_STAGE_TYPE" which is part of the "INV_TYPE" dependant list lookup ; this might already be able to contain your functional requirement?


  • 3.  RE: Clarity NSQL Lookup attributes

    Posted Sep 04, 2012 09:44 AM
    This way it will pickup the value based on the corresponding id ??


  • 4.  RE: Clarity NSQL Lookup attributes

    Posted Sep 04, 2012 09:57 AM
    oops I missed off the important bit :*)
    and lookup_code = 'xxxxxxxxxxxxx'
    xxxxxxxxxxxxx being your database value

    --

    But It all depends on how YOU set up the lookup - have an experiment to see how it all works (its fairly simple)


  • 5.  RE: Clarity NSQL Lookup attributes

    Posted Sep 04, 2012 10:05 AM
    Thanks

    Dave wrote:

    oops I missed off the important bit :*)
    and lookup_code = 'xxxxxxxxxxxxx'
    xxxxxxxxxxxxx being your database value

    --

    But It all depends on how YOU set up the lookup - have an experiment to see how it all works (its fairly simple)
    Thanks for your replies.
    I'm actually a newbie in all this so just trying to figure out how to implement your suggestion in my already existing query


  • 6.  RE: Clarity NSQL Lookup attributes
    Best Answer

    Posted Sep 04, 2012 10:38 AM
    Something like this;
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:project_phase_l:PROJECT_PHASE@ 
    , -- the rest of your NSQL stuff
    , -- the rest of your NSQL stuff
    FROM
    (
     -- the rest of your query
    , -- the rest of your query
    , ( SELECT name FROM cmn_lookups_v WHERE lookup_type = 'INV_STAGE_TYPE' and language_code = @WHERE:PARAM:LANGUAGE@ and lookup_code = inv.stage_code ) project_phase_l
    -- the rest of your query
    )
    Where you have also set up the investment stage lookup* containing ids of 'CMS_Stage0110' (etc) and names of 'Origination' (etc)
    * and this is part of (a lower level) the 'INV_TYPE' stock static dependant lookup


  • 7.  RE: Clarity NSQL Lookup attributes

    Posted Sep 12, 2012 07:12 AM
    Thanks for the guidance and solution.
    Got me in the right direction.

    I have another issue now. How do i go about it if the lookup to be used is a dynamic one. ?


  • 8.  RE: Clarity NSQL Lookup attributes

    Posted Sep 12, 2012 07:42 AM
    Well If I were you I would put the logic of the lookup in a functional and call the function in the select query. That way only the function would need to be modified for future changes. However this is a "customization" and hence may not be supported.


  • 9.  RE: Clarity NSQL Lookup attributes

    Posted Sep 12, 2012 09:05 AM

    gauripalekar wrote:

    Well If I were you I would put the logic of the lookup in a functional and call the function in the select query. That way only the function would need to be modified for future changes. However this is a "customization" and hence may not be supported.
    Yep that would work just fince - but I actually disagree that this is a customization though ; we are "allowed" to create stuff on the database like Z_ tables, so creating a Z_ function is OK as far as I am concerned!

    I normally would not bother going that far though, I would just also include the SQL logic of my dynamic lookup in the logic in my NSQL query for my portlet. (Recognising I have my code in 2 places now, but often the logic is simpler in the portlet than what is needed to drive the lookup)


  • 10.  RE: Clarity NSQL Lookup attributes

    Posted Oct 02, 2012 03:23 AM
    Thanks Dave for your help.
    It really helped me in a big way.

    Thanks


  • 11.  RE: Clarity NSQL Lookup attributes

    Posted May 02, 2014 04:04 AM

    On Clarity 13.2 I try to display project type name in my query and there is error. Below are error message and my nsql code.

    Just wonder that every static/dynamic/parameterized lookup, we always use cmn_lookups_v to mapping with readable value. Please suggestion.

    NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver]Invalid parameter binding(s). SQL Text: SELECT I.ID ID, I.CODE PRJ_CODE, I.NAME PRJ_NAME, I.IS_ACTIVE IS_ACTIVE, R.FULL_NAME PM, O.OBJ_REQUEST_TYPE PRJ_TYPE, project_type PROJECT_TYPE FROM INV_INVESTMENTS I, INV_PROJECTS P, SRM_RESOURCES R, odf_ca_project O, (SELECT name FROM cmn_lookups_v WHERE lookup_type = 'ODF_OBJECT_PROJECT.OBJ_REQUEST_TYPE' and language_code = ? and lookup_code = o.') project_type WHERE I.IS_ACTIVE = 1 AND I.odf_object_code = 'project' AND I.ID = P.prID AND I.ID = O.ID AND P.IS_TEMPLATE = 0 AND P.IS_PROGRAM = 0 AND I.MANAGER_ID = R.USER_ID AND 1=? and 1=1 .

    SELECT   

    @SELECT:DIM:USER_DEF:IMPLIED:PRJ:I.ID:ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:I.CODE:PRJ_CODE@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:I.NAME:PRJ_NAME@,
    @SELECT:DIM_PROP:USER_DEF:BOOLEAN:PRJ:I.IS_ACTIVE:IS_ACTIVE@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:R.FULL_NAME:PM@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:O.OBJ_REQUEST_TYPE:PRJ_TYPE@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:project_type:PROJECT_TYPE@
             
    FROM     INV_INVESTMENTS I,
             INV_PROJECTS P,
             SRM_RESOURCES R,
             odf_ca_project O,
             (SELECT name FROM cmn_lookups_v 
              WHERE lookup_type = 'ODF_OBJECT_PROJECT.OBJ_REQUEST_TYPE' 
              and language_code = @WHERE:PARAM:LANGUAGE@ 
              and lookup_code = o.') project_type
     
    WHERE    I.IS_ACTIVE = 1
    AND      I.odf_object_code = 'project'
    AND      I.ID = P.prID
    AND      I.ID = O.ID
    AND      P.IS_TEMPLATE = 0
    AND      P.IS_PROGRAM = 0
    AND      I.MANAGER_ID = R.USER_ID
    AND      @FILTER@


  • 12.  RE: Clarity NSQL Lookup attributes

    Posted May 02, 2014 04:20 AM

    You have a couple of problems;

    juu:

    Just wonder that every static/dynamic/parameterized lookup, we always use cmn_lookups_v to mapping with readable value. Please suggestion.

     

    Firstly CMN_LOOKUPS_V will only contain the values for static lookups, dynamic lookups will always require a query to derive the value - this is explained above.

    --

    juu:

    NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver]Invalid parameter binding(s).

    This is just an error in your code, you have an extra ' / some missing code in the line ;

    and lookup_code = o.') project_type