Oracle Date Range Problem

[reportwriter]

I'm working with a very large Oracle database, looking for records within the current month based on a date/time field. My goal is to maintain the index on the date field, limiting the time required to generate the report. The problem I'm having is this: If I convert the date/time to a date, the MonthToDate function omits data for the last day of the range. The workaround is to add 1 (+1) to the selection formula, but this causes every record in the database to be evaluated. Converting the date/time field to a string causes similar problems, since the formula DTSToDate must then be used, again causing evaluation of every record in the DB. Any help/suggestions would be greatly appreciated.

 

[MalcolmW]

Avoid using the record selection formula with large databases. Temp tables, stored procedures, views, or even the Crystal SQL Designer all offer more control over the SQL statement, and better performance.

 

[Guest_imported]

i am having the similar problem also, i just wonder if i create index for the date field , will it speed up the time?
Malcolm, for temp table/view, r u mean passing the date as parameter to create the temp table/view ?
FYI, i am using CRW 5, r the suggested solutions work fine in the version 5?

 

[MalcolmW]

As long as your ver will support parameters, this method works well. Ver 5 is not the best release CR has done - v4 was pretty good, and while 5 and 6 had a lot of enhancements, they didn't get most of it right until 7. V8, they fixed a lot of v7 bugs, and made a few new ones. I'd recommend upgrading to 7 or 8 if you can.
I prefer using stored procs, mainly cause I work mostly with SQL Server, and they are easy to create/debug. It's a bit more work with Oracle, and I've run into a people that prefer to use views or temp tables with Oracle.
Passing the parameters to the server side allows most of the processing to be done on server, and just the records you need are returned. This decreases the load on the server, the network, and the client machine, so everyone is happy.

 

[Guest_imported]

ok..i am using oracle.
for the view, is that i can write somthing like this:

create view A as
select * from table A
where A.Date in date(Date1) to date(Date2)

***Date1/Date2 is user input date

then create the report from the view data and is that i need to keep the view permanently there or is there any way to create the view only at the time the user click the button to view the report and drop it after crystalreport.action =1

Malcolm, could u pls explain further in using stored procedure, as i am pretty new on that...or do u have simple example for that.Your opinion is very helpful to me....as now i am keeping trying ..thanks a lot

 

[MalcolmW]

The intracies of Oracle are beyond my scope of knowledge. There are some knowledge base articles on the seagatesoftware.com web site on stored procs, and parameters, and I presume they have some samples there. You could also start a new thread on Oracle stored procedures with Crystal, or search for an existing one.
Sooner or later, I know I'm going to have to learn this, so if you find anything helpful, please make up a FAQ on Oracle stored procs.