AnsweredAssumed Answered

Mutli Value Lookup Parameter Mappings

Question asked by BeckyHilton on Dec 18, 2013
Latest reply on Dec 18, 2013 by N.P

We have several project lookups that are based on single select project attributes but we want to have a mutli value lookup that is based on another multivalue lookup.

This is what our typical lookup query is for a lookup that is dependant on another lookup:

SELECT
@SELECT:c.lookup_code:code@,
@SELECT:c.name:name@,
@SELECT:p.name:parent_name@
FROM cmn_lookups_v c
INNER JOIN cmn_lookups_v p ON c.parent_lookup_code = p.lookup_code AND p.lookup_type ='CISCO_PLAT_PROD_FAM' AND p.language_code ='en' AND p.is_active = 1
WHERE c.lookup_type ='CISCO_PLATFORM'
AND c.language_code ='en'
AND c.is_active = 1
and c.PARENT_LOOKUP_CODE=nvl(@WHERE:PARAM:USER_DEF:STRING:code@, c.PARENT_LOOKUP_CODE)
AND @FILTER@
ORDER BY p.name, c.name

Then I'm looking at having the @WHERE:PARAM:USER_DEF:STRING:code@ be based on a multi-select attribute on the project.  I did this but it didn't work:

SELECT
@SELECT:parent.name:org@,
@SELECT:platform.name:platform@,
@SELECT:c.lookup_code:code@,
@SELECT:c.name:name@
FROM cmn_lookups_v c
INNER JOIN cmn_lookups_v platform ON c.parent_lookup_code = platform.lookup_code AND platform.lookup_type ='CISCO_PLATFORM' AND platform.language_code ='en' AND platform.is_active = 1
INNER JOIN cmn_lookups_v parent ON platform.parent_lookup_code = parent.lookup_code AND parent.lookup_type ='CISCO_PLAT_PROD_FAM' AND parent.language_code ='en' AND parent.is_active = 1
WHERE c.lookup_type ='CISCO_PRODUCT'
AND c.language_code ='en'
AND c.is_active = 1
and c.PARENT_LOOKUP_CODE in nvl(@WHERE:PARAM:USER_DEF:STRING:code@, c.PARENT_LOOKUP_CODE)
AND @FILTER@
ORDER BY platform.name, c.name

I'm not really sure how the value is returned from @WHERE:PARAM:USER_DEF:STRING:code@ so I wasn't sure what I'd need to put in that part of the query

Outcomes