Subject | Re: Filter Problems |
---|---|
Author | Bill Gage |
Post date | 2005-02-11T00:19:04Z |
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 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