Ole,

<< I might be wrong, but since this is an equi join, I'd think it's safe to
optimize the query by applying the filter condition on both tables: >>

You are wrong in this case - it's a LOJ, hence t2's row values cannot be
evaluated until after the join is executed and any NULLs have been generated
from the join condition.

--
Tim Young
Elevate Software
www.elevatesoft.com

Robert,

<< Not with SUM? >>

How so ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< You're overstating the case here, and frankly, I don't have the time
right now to spend 20 or so messages arguing the point. >>

You don't have to spend a single message on it. There's no way you can argue
the point.

<< The fact is, DBISAM requires you to put any grouped, ordered, or having
columns in the SELECT list.  Love it or hate it, that's the case. >>

Right, and that's why DBISAM has serious flaws in how grouped queries are
evaluated. I certainly don't love it, but I'm in no desperate need to hate
it either, since I'm not using DBISAM. The reason I've contributed to this
thread is that I noticed another user having problems with a perfectly
straightforward query because of these flaws.

Ole

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:F44C021C-C6D2-4DDD-8278-2BDE2F0E84D1@news.elevatesoft.com...
> Robert,
>
> << Not with SUM? >>
>
> How so ?
>

SELECT SalesmanID FROM orders HAVING SUM(Amount) > 1000000 group by
SalesmanID

The above would probably be a subquery, with the main query selecting name,
phone number, whatever.

I want the salesmen who sold more than a million dollars, so I can send them
for two weeks in Hawaii, all expenses paid.

How else would you give the year end bonuses at Elevate?

Robert

Tim,

<< You are wrong in this case - it's a LOJ, hence t2's row values cannot be
evaluated until after the join is executed and any NULLs have been generated
from the join condition. >>

The WHERE clause condition prevents the result from including any rows with
the t2.fk column having the null value.

Other database engines do optimize the query, so it's certainly doable.

Ole

Hello Ole,

> "since I'm not using DBISAM"

How so ? I remember the good old times when ....

Anyhow, is this something the other SQL Database
systems do and not DBISAM ?

eg. MySQL, Oracle, MS SQL etc ...

--
Charalabos Michael - [Creation Power] - http://www.creationpower.com -
http://www.creationpower.gr

Hi Mihail,

<< Anyhow, is this something the other SQL Database systems do and not
DBISAM ? >>

Yes, DBISAM has a very proprietary way of evaluating the GROUP BY and HAVING
clauses, and because of this chokes on correct, valid SQL that executes in
any other RDBMS I know of.

Ole

Hello Ole,

> Yes, DBISAM has a very proprietary way of evaluating the GROUP BY and HAVING
> clauses, and because of this chokes on correct, valid SQL that executes in
> any other RDBMS I know of.

Well, if this is a "standard" in SQL world then
DBISAM should behave as the other RDBMS.

(My opinion, Tim will decide)

Greets from Greece!

--
Charalabos Michael - [Creation Power] - http://www.creationpower.com -
http://www.creationpower.gr

Ole,

<< The WHERE clause condition prevents the result from including any rows
with  the t2.fk column having the null value.

Other database engines do optimize the query, so it's certainly doable. >>

Yes, you are correct - my mistake.  What we need is an evaluation to
determine if the comparison is a NULL comparison, and if not, go ahead and
evaluate before the joins.  Of course, more complex expressions can get
quite a bit trickier, so I'll have to see what is involved.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< Right, and that's why DBISAM has serious flaws in how grouped queries are
evaluated. I certainly don't love it, but I'm in no desperate need to hate
it either, since I'm not using DBISAM. >>

You've now stated "has serious flaws" several times, so by all means please
proceed on to a product that better serves your needs.  Obviously, DBISAM is
not for you.  Perhaps our upcoming version will be, but that is for you to
decide.

--
Tim Young
Elevate Software
www.elevatesoft.com