Bill

HAVING seems to do what you want in my test example. Why doesn't it
work for you?

Regards

Bob

--- In IBObjects@yahoogroups.com, "Bill Gage" <wcgage@y...> wrote:
>
> Let me expound on how this is useful and what it would be similar
to
> in SQL
>
> it would be like writing the following
>
> SELECT Count(*), PRODUCT_CODE, SUM(BALANCE) BAL FROM ACCOUNTS
GROUP
> BY PRODUCT_CODE HAVING Count(*) > 10
>
> How this is useful is that you really only want to see the sum of
> the balance for products that have a count over 10.
>
> Now I COULD go in and dynamically rewrite the SQL statement.
> However this is a bit inefficient when I can query all of the
> records and then just filter the results on the screen.
>
> Now if applying a filter on TIBOQuery causes the dataset to be
> requiried then I may as well just dynamically write the SQL
> statement.
>
> But I would hope that the Filter would work to reduce the records
I
> am getting back.
>
> Bill
>
>
> --- In IBObjects@yahoogroups.com, "Bill Gage" <wcgage@y...> wrote:
> >
> > You'll note the Group by so there IS more than one record.
> >
> >
> > --- In IBObjects@yahoogroups.com, "Bob Zirbel" <bobz22001@y...>
> > wrote:
> > >
> > > Bill
> > >
> > > I'm not sure what you are trying to do is valid.
> > >
> > > Your query returns only one record anyway because of Count
(and
> > SUM)
> > > so it doesn't make sense to apply a filter.
> > > All you can do is restrict the number of records included in
the
> > > Count by filtering on an actual database field like
Product_Code.
> > >
> > > If you explain what you are trying to do, I may be able to
help
> > you
> > > with the SQL.
> > >
> > > Regards
> > >
> > > Bob
> > >
> > >
> > > --- In IBObjects@yahoogroups.com, "Bill Gage" <wcgage@y...>
> wrote:
> > > >
> > > > v 4.2G
> > > > using TIBOQuery and TIBODatabase
> > > >
> > > > I have been having wierd results trying to use the Filter
> > property
> > > > of IBObjects.
> > > >
> > > > I have a simple query that is like the following
> > > >
> > > > SELECT Count(*), PRODUCT_CODE, SUM(BALANCE) BAL FROM
ACCOUNTS
> > > GROUP
> > > > BY PRODUCT_CODE
> > > >
> > > > it runs fine
> > > >
> > > > I try to set the filter to the following
> > > > COUNT > 10
> > > >
> > > > I get the following error
> > > > SQL error code = -104
> > > > Token unknown - line 2, char 12
> > > > >
> > > >
> > > > I even tried the above on other queries doing the same thing
> and
> > > > against different databases and still get the same error.
> > > >
> > > > I tried changing the filter to
> > > >
> > > > COUNT = 10
> > > >
> > > > and I get...
> > > > SQL error code = -104
> > > > Token unknown - line 2, char 12
> > > > =
> > > >
> > > > Then I have tried filtering off other fields
> > > > PRODUCT_CODE = 'L11'
> > > > this worked
> > > >
> > > > I tried another field
> > > > BAL > 1000
> > > >
> > > > and I get the error
> > > > SQL error code = -206
> > > > column unknown
> > > > BAL
> > > >
> > > > Is there some sort of setting I need to make to get the
filter
> > to
> > > > function consistently?
> > > >
> > > > Or is there a bug with the filtering in version 4.2?
> > > >
> > > > Bill