Subject Re: Filter Problems
Author Bill Gage
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