AnsweredAssumed Answered

NSQL Dynamic Lookup using parameter

Question asked by Plidian on Sep 26, 2012
Latest reply on Sep 27, 2012 by Plidian
My requirement i have is to link an idea to a project that is part of a particular project type and a task under that project.
Initially the requirement was just for the project linking so I wrote a simple NSQL dynamic lookup that retrieved the projects that fit the appropriate criteria.
Then they asked if we could link to the task as well.
I read some posts on the forum that indicated dynamic nested NSQL was not an option. So I decided to try building a lookup based on the Database value.
Basically when the dynamic lookup I created does it's thing the internal ID is written to the database. To that end I wrote the following lookup
SELECT  @SELECT:prname:name@,
        @SELECT:prid:ID@,
        @SELECT:prstart:prStart@
FROM    inv_investments join PRTASK on prtask.prprojectid=inv_investments.id
WHERE   @FILTER@
AND     inv_investments.id=(select tm_task_prj from odf_ca_idea where id=@WHERE:PARAM:XML:INTEGER:/data/id/@value@)
this lookup unfortunately returns 0 results. A sql trace shows that the variable @WHERE:PARAM:XML:INTEGER:/data/id/@value@
is being populated with the value "2" no idea why.
To check my syntax I created a query and a portlet, this works just fine
The code for that is
SELECT   @SELECT:DIM:USER_DEF:IMPLIED:IDEA:PRID:PRID@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:PRNAME:NAME@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:PRSTART:PRSTART@
FROM    inv_investments join PRTASK on prtask.prprojectid=inv_investments.id
WHERE   @FILTER@
AND     inv_investments.id=(select tm_task_prj from odf_ca_idea where id=@WHERE:PARAM:XML:INTEGER:/data/id/@value@)
All that background to ask this question: What am I doing wrong? I've read many forum posts and looked at the seeded examples, while none of them are on the Idea object I've seen multiple instances of folks using these parameters in their dynamic lookups.

can anyone think of a better way to link an idea to a project and it's task?
The usecase is that not all ideas become projects some become tasks on a static list of projects and it would be nice to see/report on that.

I'd rather not write a process that xog's in an update to a static dependent set of lookups but that might be my only choice.

Thank you for your help.

Outcomes