Hello Everyone

We have a table with an Oracle field called SOMEDATE which is HYYMDS.

We report against this table and often show the output grouped by Month Name (Jan-Dec). In order to do this we define a field like this...

DEFINE FILE XXX
MONTH_COMPONENT/A3 = HNAME(SOMEDATE, 'MONTH', 'A3');
END
TABLE FILE XXX
SUM SOMEFIELD
BY MONTH_COMPONENT
END

When we look at the generated SQL that is sent to Oracle, the data is not aggregated, and is sent back without the grouping by month. So all of the data is returned and then WebFocus does another pass at the data and groups it by month.

Of course in Oracle, grouping the data by month straight away is very easy to accomplish by using TO_CHAR(SOMEDATE, 'MON').

The question is this: is there a way to return the data from Oracle with the data already grouped by month. We are in fact talking about potentially millions of rows that are being returned from Oracle when we could have Oracle return the data already grouped by month.

Is there a way to accomplish this without using SQL Passthru?

On a related note, using this...

WHERE MONTH_COMPONENT EQ 'JAN' is very slow. When in Oracle TO_CHAR(SOMEDATE, 'MON') = '1' is very fast.

Thanks

Jodye

jodye,
You're probably scanning the whole table. Remember that Oracle doesn't know what defines are. Try moving it to a WHERE clause that Oracle can understand and get rid of the define.
Good Luck,
Carol

Hi Carol

Actually I simplified the code for the purposes of my post. In fact the query is extremely efficient and has about 8 wheres in it. We are using an Oracle index. We always limit it to dates between X and Y. But we still want to group by month and we want it returned from Oracle ALREADY grouped by month.

Thanks.

Hi Jodye,
I would put it in a Focus HOLD file and then do the group by. Just wondering why you need to do it in Oracle?

Hi Carol

We want to do it in Oracle because WebFocus is downloading hundreds of thousands of records for nothing.

I am thinking that doing it in a hold file will serve no purpose. In both cases, Webfocus is doing the following: Pass1=download all records. Pass2=group by month. If I explicitly state to use a hold file, it doesn't change the fact that the first pass is still downloading records for nothing. They should ALREADY be grouped by Oracle via the TO_CHAR function. Unless I am missing something here?

Thanks for helping me wrap my head around this Carol.