Error message only generated when select statement contain GROUP BY clause,
it does not happen on Select From Where clause without Group By clause




________________________________
From: "Hardy ," <hardy30@...>
To: IBObjects@yahoogroups.com
Sent: Tue, August 10, 2010 11:21:40 AM
Subject: Re: [IBO] Select with Group By error


Sorry, confusing the field list on Table A should be: column1, column2, column3,

and colum4
and the select query should be,

Select column2, sum(column3) Tot1, sum(column4) Tot2
group by column2.

1) Below is my setting that produce error message when running in IDE
I link TIBOQuery to TDataSetProvider
and I link TCDSProvider to TDataSetProvider
and then TDataSource link to TCDSProvider
and then TDataSource link to TDBGrid
Note: TCDSProvider component from Midess Essential Pack

Inside IDE:
- When I set TIBOQuery.Active to True it is fine no error message (this means
the select statement is correct)
- When I set TCDSProvider.Active to True it is fine and datas shown in the grid
without any error message

The error message happen when I run from the IDE (Press F9 from IDE)

Error message "Column1 is not found"

2) Then I do a bit of research below:
I link TIBOQuery to TDataSource
and then TDataSource link to TDBGrid
(I do not use TDataSetProvider and TCDSProvider

Then I re-run the program from the IDE (Press F9 from IDE) the error message is

gone.

Any idea what causing this ...?

________________________________
From: Svein Erling Tysvær <svein.erling.tysvaer@...>
To: "IBObjects@yahoogroups.com" <IBObjects@yahoogroups.com>
Sent: Mon, August 9, 2010 7:44:14 PM
Subject: RE: [IBO] Select with Group By error

>I have table with following columns
>
>Table A
>* column-1
>* column-2
>column-3
>column-4
>
>my Select clause as follow:
>Select column2, sum(column-3) Tot-1, sum(column-4) Tot-2
>group by column2
>
>I use TIBOQuery version 4.8.7 to generate the Group by Select
>
>When I run the program it is looking for Column-1
>with message Column-1 is not found
>
>If I run this SQL in IBExpert, it is work well ?
>
>Did I missing something .....? Please help

I'm confused for several reasons. First, '-' is to me a minus sign and I would
expect you to have to write sum("column-3") "Tot-1" rather than sum(column-3)
Tot-1 if you insist on using that character in your field names (and adding
quotes makes the name case sensitive, so it might be "COLUMN-3"). Secondly, your

select clause doesn't even include "Column-1" at all, so how can it complain on
that field? Have you put it into a property? E.g. if you have "Column-1" in
KeyLinks, then KeyLinks should uniquely identify every row in your result set,
and to be able to do that, the row must be in the result set. If that is the
case, I'd probably just remove "Column-1" from KeyLinks and trust
KeyLinksAutoDefine to be able to sort things out (I assume your query not to be
updatable). If not, well, where does "Column-1" come from?

HTH,
Set

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]