Developing a Workaround for Date Columns Which are Indexed

With the Noetix EUL Generator for Discoverer, all columns which are indexed are associated with a class.  Classes are used to generate LOVs.  While this approach is generally helpful, end users find that parameters created on these indexed date columns "muck up" the select by calendar feature.  Here is an example of this:

 

Notice that only the current date is available.

Proposed solution:

1. Contact Noetix and request an enhancement.  I probably should do this at some point.
2. Manually correct this after a regeneration using the Discoverer Administrator.    

select

count(1)

from

eul5_expressions

where 1=1
and exp_type = 'CO'

and exp_data_type =4

and it_dom_id is not null;

I calculate 2,255 scenarios where this problem occurs.  I would rather not take this approach.

3. Create a DML script which updates these columns that fit this scenario.

Pros:  Efficient, if the table(s) are modified correctly.

Cons:  Possibly corrupt the EUL. 

Planning for approach #3:

I modified a date column which is indexed (back and forth) using the Discoverer Administrator and the two changes I have identified are as follows:

Nullify the EUL5_EXPRESSIONS.IT_DOM_ID column and increment the NOTM column (number of times modified).

I check the EUL5_EXPRESSIONS table for constraints associated with these columns using this query:

SELECT cols.table_name,

  cols.column_name,

  cols.position,

  CONS.constraint_type

FROM all_constraints cons,

  all_cons_columns cols

WHERE cols.table_name    = 'EUL5_EXPRESSIONS'

AND cons.constraint_name = 'EUL5_IT_DOM_FK'

AND cons.owner           = 'EUL5_US'

AND cons.constraint_type = 'R'

AND cons.constraint_name = cols.constraint_name

AND CONS.OWNER           = COLS.OWNER

ORDER BY cols.table_name,

  cols.position;

I do notice the foreign key constraint, EUL5_IT_DOM_FK, associated with the EUL5_EXPRESSIONS table.  Consequently, if the IT_COM_ID  is not null, then there needs to be referential integrity with the EUL5_DOMAINS table (the DOM_ID column to be exact).

Based on this analysis, I execute and commit this DML script:

update eul5_expressions

set it_dom_id = null,

notm = notm + 1

where 1=1
and exp_type = 'CO'

and exp_data_type =4

and it_dom_id is not null;

I presently am testing this in a development environment and I would like to confer with other Discoverer users (e.g. using the Discoverer Forum).

Summary

Updating the EUL using scripts is usually to be avoided.  In fact, it should be a last resort.  I am going to contact Noetix on this, but I suspect I will be left having to implement approach 3 (after more research and testing).