Clarity

Hi,   I’m attempting to write a query that will pull Multi-Value lookup results from the project object but sadly my attempts have been in vain.        Background:     We have a multi-valued field in our project properties which we use to identify   systems  (lookup) impacted by the project.   As the results from multi-value fields cannot be configured to display in the project listing view, I need to develop and SQL statement.      Can anyone help..??

Hi,                                    Multi valued lookups is stored in "odf_multi_valued_lookups" table. You can join master table and odf_multi_valued_lookups table, then you can get the master details with multilookuped values.        Sample query:      SELECT * FROM INV_INVESTMENTS INV , ODF_MULTI_VALUED_LOOKUPS MUL WHERE   INV .ID = MUL . PK_ID AND INV . CODE = 'zzTest' AND MUL .OBJECT= 'project' and mul .ATTRIBUTE = 'cont_improve_outcome'      Thanks  Senthil.

Thanks  Senthil much appreciated.  The statement works fine and returns the expected data                                        SELECT NAME, CODE, VALUE
                                    FROM niku.INV_INVESTMENTS INV, niku.ODF_MULTI_VALUED_LOOKUPS MUL
                                    WHERE INV.ID = MUL.PK_ID AND INV.CODE = 'SE728100' AND MUL.OBJECT='project'    However, and I apologise for this..   (as I've not had a lot of experience with SQL) when I remove the reference to the INV.CODE in the Where clause(as I wish to see all investments) the results are incorrect..??   The Value returned is fine but the Name and Code is repeated..                                        SELECT NAME, CODE, VALUE  
                                   FROM niku.INV_INVESTMENTS INV, niku.ODF_MULTI_VALUED_LOOKUPS MUL
                                  WHERE MUL.OBJECT='project'                                  Cheers,CAMS                                                  

Hi,  If you want to fetch all the investments, you might want to include -  INV.ID = MUL.PK_ID also.So your query should, perhaps, be as below:                                      SELECT NAME, CODE, VALUE  
                                   FROM niku.INV_INVESTMENTS INV, niku.ODF_MULTI_VALUED_LOOKUPS MUL
                                  WHERE MUL.OBJECT='project' AND INV.ID = MUL.PK_ID  Hope this helps.    Regards,Shruthi

Hi All,  I have read this email trail / discussion with great interest as I am trying to do exactly the same thing.  This is my SQL query:   SELECT NAME , CODE , VALUE FROM niku . INV_INVESTMENTS INV , niku . ODF_MULTI_VALUED_LOOKUPS MUL  WHERE INV . ID = MUL . PK_ID AND INV . ODF_OBJECT_CODE = 'Project'      The result is only returning the ID from the lookup.   How do I return the Lookup Value instead?       Thank you in advance.

Hi,         Yes. The name and code is repated but value is not repeated. because value is morethan one record in multivalued lookups for the same project. if you want to display a single rows with multivalued lookups, just create a function and call the select query.    Please try the following code....   CREATE OR REPLACE Function ZZ_MULTI_LOOKUPS_CONCAT  ( PID NUMBER , ATTR VARCHAR2 , OBJ VARCHAR2 )   RETURN varchar2  IS val varchar2 ( 2000 );   --country varchar2(100);        cursor c1 is   select VALUE from ODF_MULTI_VALUED_LOOKUPS where PK_ID = PID AND ATTRIBUTE= ATTR AND OBJECT = OBJ ;     BEGIN VAL := '' ; for cs in c1  Loop VAL :=concat( VAL , cs .VALUE);  VAL :=concat( VAL , '; ' ); end Loop;    RETURN VAL ;    EXCEPTIONWHEN OTHERS THEN  raise_application_error (- 20001 , 'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM); END;/     ThanksSenthil.       SELECT DISTINCT NAME, CODE , ATTRIBUTE, ZZ_MULTI_LOOKUPS_CONCAT ( MUL . PK_ID , MUL .ATTRIBUTE, MUL .OBJECT)VALUE  FROM INV_INVESTMENTS INV , ODF_MULTI_VALUED_LOOKUPS MUL  WHERE MUL .OBJECT= 'project' AND INV .ID = MUL . PK_ID -- AND CODE='zzTest'

Thanks!   I'll give it a try

Hi,           Just add one more join for lookup query for exisiting query. you can get the data for both lookup code and name itself. please look at the sample query.     SELECT INV .NAME "Project Name" , ATTRIBUTE, CODE , VALUE, LOOKUP .NAME  FROM INV_INVESTMENTS INV , ODF_MULTI_VALUED_LOOKUPS MUL , ( SELECT LOOKUP_CODE , NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE = 'PROC_PROCESS' AND LANGUAGE_CODE = 'en' ) LOOKUP  WHERE INV .ID = MUL . PK_ID AND INV . ODF_OBJECT_CODE = 'project' AND MUL .VALUE= LOOKUP . LOOKUP_CODE      Thanks  Senthil

Thank you, but is this code for Clarity 12.0.4 database structure?   I cannot see the table CMN_LOOKUPS_V and the query returned 0 rows.

Hi,                 CMN_LOOKUPS_V is not an table. This is a system views. This views present for clarity 7.5.2 to above version (working from clarity 7.5.2 version not sure the previous version) and also you have to change the lookup_type whatever configured in your system (Lookup ID) and then try? you can get the data, whaterver data is having in multilookup values based on the project object.  Try the below query:   SELECT INV .NAME "Project Name" , ATTRIBUTE, CODE , VALUE, LOOKUP .NAME  FROM INV_INVESTMENTS INV , ODF_MULTI_VALUED_LOOKUPS MUL , (SELECT LOOKUP_CODE , NAME FROM CMN_LOOKUPS_V WHERE LANGUAGE_CODE = 'en' -- AND LOOKUP_TYPE = 'PROC_PROCESS'  ) LOOKUP  WHERE INV .ID = MUL . PK_ID AND INV . ODF_OBJECT_CODE = 'project' AND MUL .VALUE= LOOKUP . LOOKUP_CODE        Thanks  Senthil.

Thank you for taking the time to help me.   I really appreciate it.   This query seems to work for me!     SELECT inv . name "Project Name" ,  inv . code "Project ID" ,  lv . name "Fund Sources"  FROM niku . INV_INVESTMENTS inv ,  niku . ODF_MULTI_VALUED_LOOKUPS mv ,  niku . CMN_LOOKUPS_V lv    WHERE inv . odf_object_code = 'project'   AND mv . attribute = 'dpif_att_prj_exfund' -- {name of attribute on object}   AND mv . pk_id = inv . id    and mv . value = lv . lookup_code  and lv . language_code = 'en'

Thank you, Senthil.   I used your query and it also worked.   That's fantastic.  Am I able to add the OBS unit in to filter on e.g. all projects within a specific OBS?    I cannot find the attribute for OBS in the Project object.

Hi,                 Project OBS details is stored in PRJ_OBS_ASSOCIATIONS Table whenever add the obs unit in project. just add the PRJ_OBS_ASSOCIATIONS tabls for existing query and put the joins, then only you can fetch the data based on the OBS Unit. Please use the sample query. you can change the query based on the requirements.       SELECT INV .NAME "Project Name" , ATTRIBUTE, CODE , VALUE, LOOKUP .NAME, OBS . UNIT_ID  FROM INV_INVESTMENTS INV , ODF_MULTI_VALUED_LOOKUPS MUL , PRJ_OBS_ASSOCIATIONS OBS , (SELECT LOOKUP_CODE , NAME FROM CMN_LOOKUPS_V WHERE LANGUAGE_CODE = 'en' -- AND LOOKUP_TYPE = 'PROC_PROCESS'    ) LOOKUP  WHERE INV .ID = MUL . PK_ID AND INV . ODF_OBJECT_CODE = 'project' AND MUL .VALUE= LOOKUP . LOOKUP_CODE  AND INV .ID = OBS . RECORD_ID AND OBS . UNIT_ID = 5002421      Thanks  Senthil.