Subject | Re: Filter Problems |
---|---|
Author | Bob Zirbel |
Post date | 2005-02-11T04:13:28Z |
Bill
HAVING seems to do what you want in my test example. Why doesn't it
work for you?
Regards
Bob
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