× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi,

I suspect something else.

Matt, could you please post your select statement with the UDFs?

It depends on how the statement looks like. It could be that instead of
using an index a table scan gets performed. Indexes can only be built over
original columns of the table. If a column gets changed, i.e. using a UDF,
an index over the column cannot be used anymore.

In the following example and index over MyDate cannot be used and a tables
scan gets performed:
Select * From MyFile 
   Where CvtNumToDate(MyDate) = Current_Date

In the following example an index could be used, but the UDF to convert the
Current Date must be called for each row.
Select * From MyFile 
   Where MyDate = CvtDateToNum(Current_Date)

If you use these statements in embedded SQL, it's much better to determine
the numeric date that is compared with MyDate in a separate statement. Also
a convertion of the selected dates should be performed in separate
statements:

Example in a SQL described stored procedure:

Set NumDate = CvtDateToNum(Current_Date);
Declare MyCsr Cursor For
   Select  MyDate, MyFld1, MyFld2
      From MyTable 
      Where MyDate = NumDate;
Open MyCsr;

If a table scan will be performed instead of an index access, creating a
view won't help either. When using a view the SQL-Statement gets rewritten
with the information stored in the view and after a temporary table (view
materialization) may be built and populated. 

Birgitta

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von rob@xxxxxxxxx
Gesendet: Donnerstag, 29. Juni 2006 19:37
An: Midrange Systems Technical Discussion
Betreff: RE: AW: SQL - Calculate the number of hours between 2 timestamps

An alternative might be to create a view that uses that formula.  Then you 
could just query that view.

Rob Berendt

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.