× 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.



Listers,

I have created a query that uses the CTE functions something like;

With t1 as (
select distinct a.KeyFld, a.Stsfld, b.StartDate, b.EndDate
from FileA a join FileB b on a.KeyFld = b.KeyFld
where a.StsFld like 'F%'
and Date2 > CURDATE()
order by a.KeyFld),

t2 as (
select distinct c.KeyFld
from FileC c join T1 on c.KeyFld = a.KeyFld
where C.Code in ('L10', 'L14', 'L30', 'L31', 'L16',
'L28', 'L18', 'L26', 'L22', 'L24',
'L38', 'L11', 'L55', 'L34')
and c.TranDate between b.StartDate and b.EndDate
order by c.KeyFld)

select decimal(Row_Number() over (order by d.KeyFld),5,0) as Nbr,
d.KeyFld, d.Name
from FileD d join T1 on d.KeyFld = a.KeyFld
where not d.KeyFld in (select C.KeyFld from t2)
order by d.KeyFld

The question is on the "decimal(Row_Number() over (order by d.KeyFld),5,0)
as Nbr" entry. When that is part of the select, the query performs great,
and gives me a list in a flash - less than one second. When I remove that
from the select, it takes over 10 minutes - then I kill the query because it
is taking 75% of the CPU. Any thought why having the row_number function
over the Key field would speed put the query to such an extent?

Jim

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.