To make the code easier to read (and perform slightly better), remove your GROUP clause. You are not doing any SUM, MIN, MAX etc which requires grouping.
In relation to:
Mnemonic (C.Code) / Project Name (INV.Name) / Opportunity_ID (I.C_Opportunity_ID) as filter options.
C.Code - Clarity would have created a default look-up for this master table (I am assuming this is a master table, based on your join back to inv_investments and the table starting with odf_ca. Have a look at that Lookup, as it will contain sample code which you can use to create your own custom lookup.
Project Name (INV.name) - Your look-up filter would be based on Inv.id - personally I like having the ability to do search for *ProjectName, instead of having to pull-up a list of project and selecting the required one
I.C_Opportunity_ID - you are going to have to give us more information on what this is, as it is a custom attribute. Potentially it is a Lookup Custom Attribute and if so, just use that look-up.
Have a look at the OOTB query Missing Status Report, where several of the returns attributes in the Query are defined as 'Look-ups'. Every one of those look-up attributes can be used in the Portlet Filter as Look-ups.