Clarity

Clarity 12.0.4.5283  I have defined a lookup with source = Dynamic Query on object Project.De query is:  SELECT   @SELECT:CASE nvl(inv.GOAL_CODE, 'Leeg')
  WHEN 'Leeg' THEN ' '
  WHEN 'Beheer' THEN 'Beheer'
  WHEN 'Onderhoud' THEN 'Onderhoud'
  WHEN 'Not_Available' THEN 'Indirect'
  ELSE 'Projecten'
 END:myCategory@
FROM INV_INVESTMENTS inv
WHERE 1 = 1
AND inv.ID = @WHERE:PARAM:XML:INTEGER:/data/id/@value@  The intention is to derive the value from another lookup (goal).The result, when displayed on a project properties page, and when the value of goal has been changed and when SAVE has been pushed, that I still have to select the derived value. It displays [--Select--] and the drop down gives me the choice between [--Select--] and the derived value.I tried to make the attribute read-only, but for that a default must be specified and that is not possible for a Dynamic Query Lookup attribute.  All suggestions how to overcome this are welcome.  Thanks,Kees

I had a similar problem recently.  I solved it (in my case) by making my lookup return 2 values; "1" and the calculated value. But I made the"hidden key" my dummy "1" value and the "Display Attribute" to be my calculated value.  I was then able to specify a default value of "1" in the attribute in the object and make the attribute read only.   I then only put the attribute on the EDIT layout of my object (so it is not on the CREATE layout).  This meant I never got the "[--Select--]" value displayed.  Also "my" solution means that the attribute on the database is always "1", but my data-entry screen works well.  Pehaps something like that could help you?    I mean by changing the NSQL to something like;    SELECT    @SELECT:1:DUMMYID@,@SELECT:CASE nvl(inv.GOAL_CODE, 'Leeg')
  WHEN 'Leeg' THEN ' '
  WHEN 'Beheer' THEN 'Beheer'
  WHEN 'Onderhoud' THEN 'Onderhoud'
  WHEN 'Not_Available' THEN 'Indirect'
  ELSE 'Projecten'
 END:myCategory@
FROM INV_INVESTMENTS inv
WHERE 1 = 1
AND inv.ID = @WHERE:PARAM:XML:INTEGER:/data/id/@value@    Dave.    EDIT : My query also "always" returns a value (even when previewed),  you could try;     SELECT    @SELECT:1:DUMMYID@,@SELECT:X.myCategory:myCategory@FROM(SELECT( SELECT CASE nvl(inv.GOAL_CODE, 'Leeg')
  WHEN 'Leeg' THEN ' '
  WHEN 'Beheer' THEN 'Beheer'
  WHEN 'Onderhoud' THEN 'Onderhoud'
  WHEN 'Not_Available' THEN 'Indirect'
  ELSE 'Projecten'
 END:myCategory@
FROM INV_INVESTMENTS inv
WHERE inv.ID = @WHERE:PARAM:XML:INTEGER:/data/id/@value@)FROM DUAL) X
WHERE @FILTER@  (that should make sure you always get a value, even it is "null")  (Note that this is an Oracle solution)[left] Message Edited by Dave on 22-12-2009 01:07 PM [left]

Thanks Dave,  However, the first query still doesn't allow me to set a default.  And the second query gives me a syntax error:  ODF-0001: Unable to register dynamic query lookup: Could not retrieve or register the nsql::NsqlLookupHandler::updateNsql() =>NSQL Syntax exception: NSQL_SYNTAX_ERROR_DB_ERROR, Info: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00923: FROM keyword not found where expected SQL Text: SELECT 1 DUMMYID ,X.myCategory myCategory FROM ( SELECT ( SELECT CASE nvl(inv.GOAL_CODE, 'Leeg') WHEN 'Leeg' THEN ' ' WHEN 'Beheer' THEN 'Beheer' WHEN 'Onderhoud' THEN 'Onderhoud' WHEN 'Not_Available' THEN 'Indirect' ELSE 'Projecten' END:myCategory@ FROM INV_INVESTMENTS inv WHERE inv.ID = ?) FROM DUAL ) X WHERE 1=? and 1=1 .  Furthermore, when yoy say that [ "my" solution means that the attribute on the database is always "1", but my data-entry screen works well] means that you cannot report and / or filter on the calculated attribute?

I think I made a typo ; a missing ")"  Try;    SELECT    @SELECT:1:DUMMYID@,@SELECT:X.myCategory:myCategory@FROM(SELECT( SELECT CASE nvl(inv.GOAL_CODE, 'Leeg')
  WHEN 'Leeg' THEN ' '
  WHEN 'Beheer' THEN 'Beheer'
  WHEN 'Onderhoud' THEN 'Onderhoud'
  WHEN 'Not_Available' THEN 'Indirect'
  ELSE 'Projecten'
 END:myCategory@
FROM INV_INVESTMENTS inv
WHERE inv.ID = @WHERE:PARAM:XML:INTEGER:/data/id/@value@)) FROM DUAL) X
WHERE @FILTER@    --  And "yes" because I do not persist the "caluclated value", when I do any report/filtering on it I have to include the calculation also in the reporting and filtering logic.  (I actually put "my calculation" in a single parameterised database FUNCTION and just call that from my lookup, from my reporting portlet, in my reporting portlet filters etc etc etc  By having "my calculation" in the database function I only need to specify the calulation logic once, in a single place, and it is used by all things that reference that calculation.)    

Thanks again Dave,  But I still get the same syntax error. I am afraid that my knowledge of SQL is minimal. Sorry.

OK I actually ran this one into Clarity (rather than guessing at the syntax!)   :-)   SELECT   @SELECT:1:DUMMYID@  ,@SELECT:X.myCategory:myCategory@  FROM  (  SELECT  ( SELECT CASE nvl(inv.GOAL_CODE, 'Leeg')    WHEN 'Leeg' THEN ' '    WHEN 'Beheer' THEN 'Beheer'    WHEN 'Onderhoud' THEN 'Onderhoud'    WHEN 'Not_Available' THEN 'Indirect'    ELSE 'Projecten'   END  FROM INV_INVESTMENTS inv  WHERE inv.ID = @WHERE:PARAM:XML:INTEGER:/data/id/@value@  ) myCategory FROM DUAL  ) X  WHERE @FILTER@     I had got all my @ stuff in the wrong place!    

OK, I can now set a default value (1) and make the calculated attribute read-only.But, whatever value I give to Goal, the calculated attribute displays nothing. When I make it editable again, the choice is again between [--Select--] and the derived value.

You have to "Save" the record after you have selected a new "Goal"?  Have you associated the correct parameter ([--Object Id--]) with the (new dummy) attribute in the object definition?  Are you sure that the values you have hard-coded in the "lookup logic" are correct? (I can't comment on how you may have redefined the "Investment Goal / INVESTMENT_GOAL_TYPE" lookup - but in a "stock Clarity" system the GOAL_CODE holds values like "AVOID_COST", "GROW_BUSINESS" etc)?  (Could you just achieve what you are trying to achive by redfining the values in that lookup anyway?)  --    I might be out of ideas now though.....    

Yes, I Save after I change the Goal value.  I don't understand what you mean with Have you associated the correct parameter ([--Object Id--]) with the (new dummy) attribute in the object definition?   I am sure that the redefined values in Goal are correctly used in the query.I need this derived attribute for  filtering and reporting projects. I know that can be done with a multi-value filter, but than I need to specify a whole lot of vaules when I want to filter on [ projecten ]. Error prone!  Anyway Dave, thanks a lot for all your advice. I owe you.

OK I think I may have confused myself and you slightly.   :-)  In your original NSQL you use the construct;   @WHERE:PARAM:XML:INTEGER:/data/id/@value@   which picks up the project "ID" from the URL on the 'current' page.  In "my solution", instead of that construct I use a @WHERE:PARAM:USER_DEF:INTEGER:inv_id@ construct, which makes the lookup into a parameterised lookup (the parameter being "inv_id")  So what I mean by   Have you associated the correct parameter ([--Object Id--]) with the (new dummy) attribute in the object definition?   is just that when (in my solution) I am creating the "dummy" attribute against the project object (the attribute which I am associating with the new parameterised lookup), after I specify the lookup against the attribute (and press 'Save'), an extra new field appears in that attribute definition screen "Lookup Parameter mappings", where I make the relationship between the (parameterised) lookup and the associated object (so I would map "inv_id" to the [--Object ID--] )  --  Having said all that though, I don't actually see why "your method" would not work - it might just be worth trying "my method" though?

Have been away for a while. Being back I  applied your last statements, and yes now it works perfectly.Thanks again Dave.

No problem...  Just to complete the "thinking" on this, I would suspect that "your original method" (using the project "ID" from the URL on the 'current' page) may not have worked because whilst that URL value is available for use in NSQL on portlets on that page, it perhaps is NOT available to a LOOKUP called from that page...  Whilst "my" method (using parameterised lookups) worked OK because the object passes the (parameter) value to the lookup explictly.    So ; "Parameterised Lookups" - thats the way to do it!  Dave.  

UPDATE (Nov2012) : Worked example of the technique I'm attempting to describe above as a "tip" ;

Tip : How To Put Any Dynamically Generated Value On A Clarity Object