Scott,

<< I am due to the fact that DBISAM barks if I do not.. a flaw? as pointed
out by Ole Willy Tuv. >>

It's simply a limitation in the SQL in DBISAM.  You can only reference a
SELECT column in the HAVING clause, just like with the GROUP BY or ORDER BY
clauses.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< This indicates that DBISAM is processing the HAVING clause against the
result set columns and data, >>

That is the basically the definition of a HAVING clause.  The COUNT(*)
expression is an exception.

<< The HAVING clause is a filter on the groups of rows resulting from
evaluating the FROM and GROUP BY clauses, and can only specify grouping
columns and aggregate functions. >>

DBISAM allows you to include non-grouped columns in the SELECT column list,
hence the ability to use them in the HAVING clause also.  There's no harm in
doing so.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< DBISAM allows you to include non-grouped columns in the SELECT column
list, hence the ability to use them in the HAVING clause also.  There's no
harm in doing so. >>

With all respect - I don't agree. Allowing non-grouping columns in the
select list of a grouped query doesn't only break official SQL
specifications, but also the common behavior in the RDBMS industry.
Processing the GROUP BY and HAVING clauses based on the result set columns
and data further makes the query evaluation logic in DBISAM very proprietary
and out of line with other database engines.

Ole

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:BFF41903-6AE4-43B1-8126-2B4073DF87D1@news.elevatesoft.com...
> Scott,
>
> << I am due to the fact that DBISAM barks if I do not.. a flaw? as pointed
> out by Ole Willy Tuv. >>
>
> It's simply a limitation in the SQL in DBISAM.  You can only reference a
> SELECT column in the HAVING clause, just like with the GROUP BY or ORDER
BY
> clauses.
>

That was not the original problem. He had a query like

SELECT ,,, where field in (select f from table group by f having count(*) >
1)

and DBISAM complained that he was selecting two fields in the subquery (f
and count).

Seems to me that just as you can use non-selected fields in the WHERE
clause, you should be able to use non-selected fields in the HAVING clause.

Robert

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:0EE9ABE9-2ED5-4351-9487-2BCC75BEAB12@news.elevatesoft.com...
>
> DBISAM allows you to include non-grouped columns in the SELECT column
list,
> hence the ability to use them in the HAVING clause also.  There's no harm
in
> doing so.
>

There can be plenty of harm, but that's another story

Robert

Yes, and I do have that as a listed statement, but the idea was to display all the actual
duplicate record(s) for the client to review and delete the correct one.

The statement you listed below simply displays 1 instance and the count. Hard to
delete from that without a use of a rabbit's foot and a fast pair of tennis shoes when
the client deletes the wrong one!

Regards,
Scott.

"Donat Hebert (WSI)" <dhebert@worldsoftwareinc.com> wrote in message
news:86C2280C-6E4F-44D2-AA72-8827F382E14F@news.elevatesoft.com...
> Select DisplayName, Count(*) as DupRecs
> from Clients
> group by Displayname
> having DupRecs > 1
>
> works in one step...
>
> "Adam H." <info at wsd.net .au> wrote in message news:10151B01-A91C-480A-B1CD-EBF7E8208D08@news.elevatesoft.com...
>> Hi Scott,
>>
>> As an alternative, you could try:
>>
>> SELECT DisplayName Dupname, COUNT(*) as DupCount into memory\temp FROM Clients GROUP BY
>> DisplayName ;
>> SELECT DisplayName from Clients
>> join memory\temp temp on DisplayName = Dupname
>> where temp.DupCount > 1;
>>
>> Regards
>>
>> Adam.
>>
>
>

Yes, and I do have that as a listed statement, but the idea was to display all the actual
duplicate record(s) for the client to review and delete the correct one.

The statement you listed below simply displays 1 instance and the count. Hard to
delete from that without a use of a rabbit's foot and a fast pair of tennis shoes when
the client deletes the wrong one!

Regards,
Scott.

"Donat Hebert (WSI)" <dhebert@worldsoftwareinc.com> wrote in message
news:86C2280C-6E4F-44D2-AA72-8827F382E14F@news.elevatesoft.com...
> Select DisplayName, Count(*) as DupRecs
> from Clients
> group by Displayname
> having DupRecs > 1
>
> works in one step...
>
> "Adam H." <info at wsd.net .au> wrote in message news:10151B01-A91C-480A-B1CD-EBF7E8208D08@news.elevatesoft.com...
>> Hi Scott,
>>
>> As an alternative, you could try:
>>
>> SELECT DisplayName Dupname, COUNT(*) as DupCount into memory\temp FROM Clients GROUP BY
>> DisplayName ;
>> SELECT DisplayName from Clients
>> join memory\temp temp on DisplayName = Dupname
>> where temp.DupCount > 1;
>>
>> Regards
>>
>> Adam.
>>
>
>

Tim,

I don't expect that you'd do any major changes to V4 at this late stage, but
I do hope that V5 implements a more mainstream query evaluation logic.

Tip: Sub-chapter 9.2.3, starting at page 278, in Jim Melton's book provides
a good explanation of how the GROUP BY and HAVING clauses are evaluated and
processed.

Ole

Ole,

<< With all respect - I don't agree. >>

I understand - we've had this conversation at least four times.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< I don't expect that you'd do any major changes to V4 at this late stage,
but I do hope that V5 implements a more mainstream query evaluation logic.
>>

The query evaluation logic is only superficial - how the engine implements
the actual processing is irrelevant as long as the result is correct.  A
case in point is evaluating WHERE clauses before joins.  Per the SQL
statement, they should never be evaluated until after the JOINs, but there
are times when it is perfectly safe and proper to do so.

--
Tim Young
Elevate Software
www.elevatesoft.com