Hi Helen,

I have done some research based on your advice.
The following select does not generate error on TIBOQuery, because sls_id as key
and it is included in the select list

Select sls_id, sum (amount)
From SaleRevenue
Where region = 'West'
Group by sls_id

I have another case which also generate error message on TIBOQuery

table name = AccRcv
Cust_Code = varchar(10) key1
Curr_Code = varchar(3) key2
Ar_amount = numeric 11.3

So base on given table above I can generate Total of AR_Amount
a) by Cust_Code, Curr_code
b) by Cust_Code
c) by Curr_Code

This following query generate error message ...Qry_xxx: Field 'Cust_Code' not
found
Select Curr_code, sum(AR_Amount)TotAmt
From AccRcv
Group Curr_code
Having sum(AR_Amount) > 0

if I include Cust_Code on the select list this will be okay,
Select Cust_code, Curr_code, sum(AR_Amount) TotAmt
From AccRcv
Group Cust_code, Curr_code
Having sum(AR_Amount) > 0

However it is generate different result set which I do not want,
because I want sum up the amount by Curr_code only
instead of by Cust_Code, Curr_code

Thank you for your time and thought







________________________________
From: Helen Borrie <helebor@...>
To: IBObjects@yahoogroups.com
Sent: Sun, August 15, 2010 5:01:05 PM
Subject: Re: [IBO] Re:TCDSProvider does not like TIBOQuery


At 09:42 PM 15/08/2010, Hardy , wrote:

>Hi Helen, What does group by 1 means in your select statement below ?
>
>>select sls_id, sum (amount)
>>from SaleRevenue
>>Where region = 'West'
>>group by 1
>>

It means "first field in the grouping list". For this query it could be
substituted by


group by sls_id

...but it is not true that the field name can be used in all cases.

HB







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