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



Ok, Got it to show me something. The version with the Row number comes back
almost immediately with a very complex diagram. I'm not sure what I am
looking at or what it means. The version with out the ROW_NUMBER takes a
long time to come back, and shows me a much simpler diagram, but I still
don't know what it means, other than it takes a long time to run. What am I
looking for?

Jim

On 8/2/07, MKirkpatrick@xxxxxxxxxxxxxxxxx <MKirkpatrick@xxxxxxxxxxxxxxxxx>
wrote:

Depending on your naming, qualify it: LIBNAME.idate(coupdt,'*MDY')


midrange-l-bounces@xxxxxxxxxxxx wrote on 08/02/2007 12:07:44 PM:

Good suggestion Brian, but iNav SQL Scripts does not find the iDate
function
that is created on my system. How do I get it to see the function that
I
get in my green screen has no problem with? I am an iNav SQL nube.

Thanks!

Jim

On 8/2/07, Brian Johnson <brian.johnson.mn@xxxxxxxxx> wrote:

What does the Explain function in iNav run-sql-statements have to say
about it?

On 8/1/07, Jim Essinger <dilbernator@xxxxxxxxx> wrote:
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


--
Brian Johnson
brian.johnson.mn@xxxxxxxxx

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.