Hello -

I'm trying to find a way to provide defined fields in my master file that represent "current_month" and "created_month", such that the user could create an InfoAssist report by using "current_month eq created_month" in a filter, and such that the create_date index is being used in Oracle and the DB is filtering the data to limit rows to only the current month.

I can easily decompose "created_date" to its various parts to get "created_month" as a component to use, and I can create a define that represents current_month.

What I'm having trouble with is ensuring that Oracle will use an appropriate index related to "created_date".

When I use the filter "created_month eq current_month", Oracle does not receive any kind of filter at the DB level. This results in a slow query (no index being used), and significantly more data being returned to the WF server for processing.

If I create another filter: create_date from dt(03/01/2010) to dt(03/31/2010), this is passed to Oracle. It then would use the index on create_date, and filter the amount of data returned. However, this defeats the purpose of using more of a relative query that does not need to be changed each month.

Any suggestions?

Thanks,
Andy

This message has been edited. Last edited by: Andy Bass,

Always do the select based on the oracle indexed fields.
Use the defines only for retrieval of the data, but not for selecting - or maybe just a where total selection.
If you do the select on the defined fields, you probably force focus to do the retrieval and selection, whereas when you do the select against the entire field, oracle can do the select. You can create a dynamic version of the select by making use of dialog manager (&) variables.

Unless IBI had significantly enhanced the Oracle adapter in the newer WebFOCUS releases, it is not possible for the adapter as I know it to translate DATE operations (via DEFINE a/o COMPUTE) to SQL before being presented to the database.

The only way to accomplish what you need is by either of two methods (that I can think of anyway):

1. Creating an Oracle view that exposes each date component as additional columns. Something like:

create or replace view myview
select created_month, /* whole date */
       to_char(created_month, 'YYYYMM') created_yym,
       to_char(created_month, 'MM') created_month,
       ...
from mytable;

You can then safely and efficiently apply filters on created_month (as a date-time field), created_yym, created_month and so on, while keeping in mind that those new fields are *not* dates but VARCHAR2 and should be treated as such when querying them from WebFOCUS. Of course, you can later convert them to a Date / SmartDate within WebFOCUS for the purpose of presentation or extra processing.

TABLE FILE MYVIEW
PRINT
     blah
WHERE CURRENT_YYM  EQ '201003' <-- Oracle will handle the filter
END

2. Having a Date dimension table in your database in which each imaginable date piece is recorded and aply your filter on them while joining to your actual transaction of fact table.

- Neftali.

This message has been edited. Last edited by: njsden,

quote:

TABLE FILE MYVIEW
PRINT
blah
WHERE CURRENT_YYM EQ '201003' <-- Oracle will handle the filter
END

It should be obvious that you can use Dialog Manager amper variables as well:

-DEFAULTS &VYEAR_MTH='201003';
TABLE FILE MYVIEW
SUM   blah 
WHERE CURRENT_YYMD EQ '&VYEAR_MTH'
END

Do not however attempt to use a DEFINE/COMPUTE field to provide a value to your filter as it won't work efficiently.

DEFILE FILE MYTABLE
PROC_YYM/A6 = '&VYEAR_MTH';
END
TABLE FILE MYTABLE
SUM blah
WHERE CREATED_YYM EQ PROC_YYM

The code above will work but it may not be efficient as there is no way Oracle can possibly know what PROC_YYM is unless WebFOCUS does some magic there.

- Neftali.