AnsweredAssumed Answered

NSQL & Parameters

Question asked by cshah on Sep 17, 2010
Latest reply on Sep 22, 2010 by Chris_Hackett
Hello,
Sorry this is a little lengthy, but any help is greatly appreciated.

I have read the chapter on About NSQL Queries in the Studio Developer's guide regarding parameters, however, still a little shaky on how to use this feature to my advantage ofcourse.

I need to create a lookup that will use a dynamic query. I want to explain my data tables a little to explain my problem and know that I am on the right path...

I have an Object and a sub-object. The object data is in one table, we'll call it MUM, and the sub-objects data is in another table, MUT.

So 1 data value in MUM can have multiple data values in MUT. I want my lookup to display only those MUT's that corresponds to a specific MUM.
MUM.ID = MUT.ODF_PARENT_ID so i have a 1 to many relationship.

So my dilemma is depending on the MUM, my lookup should only display those corresponding MUT's.

Am I correct in thinking i need to use parameters here? I believe my situation is similar to the RISK BROWSE dynamic query.

SELECT @SELECT:RIM.ID:ID@,
@SELECT:RIM.NAME:NAME@,
@SELECT:RIM.PK_ID:PK_ID@,
@SELECT:RIM.RIM_RISK_ISSUE_CODE:RIM_RISK_ISSUE_CODE@
FROM RIM_RISKS_AND_ISSUES RIM
WHERE @FILTER@
AND RIM.TYPE_CODE='RISK'
AND (@WHERE:PARAM:USER_DEF:INTEGER:RIM_ID@ IS NULL or @WHERE:PARAM:USER_DEF:INTEGER:RIM_ID@ != RIM.ID)
AND (@WHERE:PARAM:USER_DEF:INTEGER:PK_ID@ IS NULL or @WHERE:PARAM:USER_DEF:INTEGER:PK_ID@ = RIM.PK_ID)

However, i'll be honest, I don't understand the :<PARAM_NAME> section that is happening in the last two lines above. Especially RIM_ID.
Does this correspond to anything in the Select section?

This is what I have done based on my issue. I have stuck to the MUT table only.

SELECT @SELECT:MUT.ID:MUTID@,


@SELECT:MUT.ODF_PARENT_ID:ODFID@,


@SELECT:MUT.NAME:TASKNAME@,


@SELECT:MUT.MUT_SUBTEAM:MUCCSUBTEAM@,


FROM ODF_CA_MUT MUT


WHERE @FILTER@


AND (@WHERE:PARAM:USER_DEF:INTEGER:MUTID@ IS NULL or @WHERE:PARAM:USER_DEF:INTEGER:MUT_ID@ != MUT.ID)


AND (@WHERE:PARAM:USER_DEF:INTEGER:ODFID@ IS NULL or @WHERE:PARAM:USER_DEF:INTEGER:ODFID@ = MUT.ODF_PARENT_ID)

I don't think I understand the exact secret to passing values when using parameters. Not sure what i'm doing wrong in my query as well ofcourse since it returns errors.

I need a dummy example if you don't mind.

THank you in advance

Outcomes