Roy
> Actually its about half the speed of my original script at c1.8 secs.

Interesting.  My test file had 20K records with 10 variations (ie mailboxes)
on a really crappy old machine <bg> and was returning a result in < 0.5
secs.

>There was one other tiny problem with your query - it produces the wrong
results

Hmm....   it looks like it should produce the same as your script - I can't
see why not.

> The conclusion I came to was that there were only a few mailboxes that
anyone would care about there being unread emails in ie those where emails
were received into (eg the inbox) the rest of them emails are stuffed into
after being viewed / handled.

You mean you can move mail out of your inbox ? <bg>

John

John,

<< Interesting.  My test file had 20K records with 10 variations (ie
mailboxes) on a really crappy old machine <bg> and was returning a result in
< 0.5 secs. >>

It may depend upon the RequestSensitive setting for the query, and Roy may
have been using a different setting.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>It may depend upon the RequestSensitive setting for the query, and Roy may
>have been using a different setting.

Dead right! Set RequestSensitive to true and the time is c0secs

Roy Lambert

Roy

>
> Dead right! Set RequestSensitive to true and the time is c0secs
>

I think it is being "economical with the truth" <bg>

John

Roy,

<< Dead right! Set RequestSensitive to true and the time is c0secs >>

It's one of the improvements for 2.03 - sensitive result sets with derived
tables and/or sub-queries in the SELECT list.  Such functionality can
replace most LOJ operations and still allow you to generate a sensitive
result set.  The only catch is that any "lookup" or "calculated" SELECT
columns are read-only.  For example, this query:

SELECT CustNo, Customer.Name AS CustName
FROM Orders LEFT OUTER JOIN Customer ON Orders.CustNo=Customer.CustNo

can be written to generate a sensitive result set as this:

SELECT CustNo,
(Customer.Name FROM Customer WHERE Customer.CustNo=Orders.CustNo) AS
CustName
FROM Orders

Then, if you or the user changes the CustNo column value in the result set,
the CustName column will automatically re-calculate after the row is
updated.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>SELECT CustNo,
>(Customer.Name FROM Customer WHERE Customer.CustNo=Orders.CustNo) AS
>CustName
>FROM Orders

And I like more new syntax to learn, or is the no SELECT in the subquery a typo?

Roy Lambert

ps part of another 2.03 feature in 2.02

Roy,

<< And I like more new syntax to learn, or is the no SELECT in the subquery
a typo? >>

Sorry, yes it's a typo.

<< ps part of another 2.03 feature in 2.02 >>

Is that a question ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< ps part of another 2.03 feature in 2.02 >>
>
>Is that a question ?

Only if you want to treat it as one, more just a comment.

Roy Lambert