What is wrong with this Query?(50分)

SQL Content:
Select sum('kkk') as k0
from database1
where datetime_field>=:datetime1 and datetime_field<=datetime2
The source of this Query Component:
query.datasource:=dbgrid1.datasource
The error message is:
Capability is not supported!
Why?
Thank you in advance!

时间日期字段的条件定义是有一定要求的,
请参考Local SQL帮助.

贴一段:
Description
Use EXTRACT to return the year, month, or day field from a DATE or TIMESTAMP column. If the column used with the EXTRACT function contains a NULL value, the return value of EXTRACT will be NULL. If the value is not NULL, EXTRACT returns the value for the specified element in the date, expressed as a SMALLINT.
The Extract_Field parameter may contain any one of the specifiers: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. The specifiers YEAR, MONTH, and DAY can only be used with DATE and TIMESTAMP columns. The specifiers HOUR, MINUTE, and SECOND can only be used with TIMESTAMP and TIME columns.
SELECT saledate,
EXTRACT(YEAR FROM saledate) AS YY,
EXTRACT(MONTH FROM saledate) AS MM,
EXTRACT(DAY FROM saledate) AS DD
FROM orders
The statement below uses ado
B column (containing birthdates) to filter to those rows where the date is in the month of May. The month field from thedo
B column is retrieved using the EXTRACT function and compared to 5, May being the fifth month.
SELECTdo
B, LastName, FirstName
FROM People
WHERE (EXTRACT(MONTH FROMdo
B) = 5)
Applicability
EXTRACT operates only on DATE, TIME, and TIMESTAMP values. To use EXTRACT on non-date values, first use the CAST function to convert the column to a date type.
Note: while SQL-92 provides the EXTRACT function specifiers TIMEZONE_HOUR and TIMEZONE_MINUTE, these specifiers are not supported in local SQL.

database1`是表还是数据库?
query.datasource:=dbgrid1.datasource;
是不是要倒过来?
应该先把SQL语句放在SQL Explorer中试一下,然后再用.
如果还不行,把原码发过来,我来帮您调.
huangjg@990.net

Sorry, maybe I confuse you. The following is the source code. And the SQL
contain just like this:
select sum('float_field') as hj
from table1
where datetime_field>=:date1 and datetime_field<=:date2
What I need is the value of <font color=red>hj</font>.The database is
paradox. Thank you!
procedure TProductForm.BitBtnSumClick(Sender: TObject);
var
yy1,mm1,dd1,yy2,mm2,dd2:word;
date1,date2:TDateTime;
begin
yy1:=strtoint(Edit2.text);
mm1:=strtoint(Edit3.text);
dd1:=strtoint(Edit4.text);
date1:=EnCodeDate(yy1,mm1,dd1);
yy2:=strtoint(Edit5.text);
mm2:=strtoint(Edit6.text);
dd2:=strtoint(Edit7.text);
date2:=EnCodeDate(yy2,mm2,dd2);
with QuerySumdo
begin
Close;
DataSource:=DBGrid1.DataSource;
{ <font color=red>I want to sum a column of
which display in the DBGrid1 </font>}
ParamByName('date1').Asdate:=date1;
ParamByName('date2').Asdate:=date2;
open;
Edit8.Text:=Floattostr(FieldByName('hj').AsFloat);
end;

这样写没事:
var year,month,day:word;
DecodeDate(date1, Year, Month, Day);
query1.sql.add('select sum(float_field) as hj from table1
where datetime_field>="'+inttostr(Month)+'/'+inttostr(Day)+'/'+inttostr(year)+'"');
OR:
query1.sql.add('select sum(float_field) as hj from table1
where datetime_field>="'+inttostr(mm1)+'/'+inttostr(dd1)+'/'+inttostr(yy1)+'"');
用参数SQL的没试过.

> DataSource:=DBGrid1.DataSource;
{ I want to sum a column of
> which display in the DBGrid1 }
Wrong !!!
你错误的理解了TQuery.DataSource的作用! ,

请检查以下几项
1. 在 QuerySum 属性中 params:
date1 和 date2 类型应 date
2. 设置 QuerySum 的 DatabaseName 而不是 DataSource,
QuerySum.DatabaseName := DBGrid1.DataSource.Dataset.DatabaseName

Oh, I already got the answer, I made a mistake in the SQL sentence:
> select sum('float_field') as hj
^^^^^^^^^^^^ The quotation marks must be took out.
But although I can excute the query, I can't get correct result.
As SeaSky said, the matter come from this sentence:
> DataSource:=DBGrid1.DataSource;
It always returns the sum of the float_field of all the table1. That
is to say, the above sentence is not used. WHY?
Is it wrong to evaluate one DataSource to another DataSource?

TQuery构件的DataSource属性用于指定一个数据源(TDataSource构件)，
如果应用程序既没有在设计期也没有在运行期给参数赋值，它就在这个
数据源中查找与参数名匹配的字段，然后用这个字段的值作为参数的值。

TO SeaSky,
But I really evaluate the DBGrid1.DataSource! Why?

1.datasource用错了.
你可能要再加一个参数:table1,
把dbgrid的datasource相关的表的tablename给它.

2.你的关于在sql中关于日期的写法用'mm/dd/yyyy'格式,

To wtiebo:
Delphi认为你可以这样赋值, 因为它类型兼容, 都为Tdatasource类型.
TQuery1.Datasource:

假设query1中SQL 这样写: 'Select Count(*) from Log where UserID=:UserID'
同时Query1.Datasource的值又不等于Nil ,
当打开query1时, Query1会做自动如下工作:
Query1.ParamByName('UserID').value := Query1.Datasource.dataset.fieldbyname('UserId').value;
如果 Query1.Datasource.dataset中没有Userid字段(必须和参数名同名),
^^^^^^
或Query1.Datasource.dataset没有打开.
或者数值类型不匹配.
Query1打开时, 便会出错.
当Query1.Datasource.dataset发生记录滚动, Query1便自动取
Query1.Datasource.dataset 当前记录中UserID 值, Query1会重新
查询结果.

To SeaSky:
Ido
just as you said above. But why I can't get the correct result?
It always return the sum of all records in the table.
> Query1.ParamByName('UserID').value :=
Query1.Datasource.dataset.fieldbyname('UserId').value;
<font color=blue>^^^^^^^^^^^^^^^^^^^^^^^</font> This dataset is a Query too.

你把DBGRID1.Datasource.dataset的情况告诉我,
是TTable 还是 TQuery1 , 如果是TQuery, 把SQL内容
告诉我! ;我写个例子.

我想你要的是Table中一部分记录的SUM,所以想用一个Query得到
这个数据子集，然后用DBGrid1显示他，然后统计子集的SUM,
对吗？
你为什么不用一个SQl解决呢？
DBGRID1.Datasource.dataset如果是Table你应该设Rang.

To SeaSky:
I reallydo
n't want to trouble you again, but I want to know why on
this issue, so... I am sorry!
It is a Query. The SQL sentence is:
select * from table-name
where The_NO=:number
Thank you again!

Yes, what zx said is just I want. In fact, I already solve this problem
just as zx said--use a TQuery. But when I use Datasource like above in
a QReport, I can't get the correct result.
I am sorry for my selfish. But I really want to know why! If there is
someone can solve this problem, I will to add 50 marks. ( But Ido
n't
know how todo
it, so please tell me first. )

yy1:=strtoint(Edit2.text);
mm1:=strtoint(Edit3.text);
dd1:=strtoint(Edit4.text);
date1:=EnCodeDate(yy1,mm1,dd1);
yy2:=strtoint(Edit5.text);
mm2:=strtoint(Edit6.text);
dd2:=strtoint(Edit7.text);
date2:=EnCodeDate(yy2,mm2,dd2);
with Query1do
begin

CLose;
SQL.Clear;
SQL.Add('Select Sum(float_field) as HJ from Database1 ');
SQL.Add('where The_No:=The_no');
SQL.Add('and datetime_field >=1' );
SQL.Add('and datetime_field >=2' );
Parambyname('D1').asdate:=Date1 ;
Parambyname('D2').asdate:=Date2 ;
Datasource := DBGrid1.Datasource;
//注 参数The_no的数值由 DBGrid
// 所对应Query的当前记录提供,
Open ;
Edit8.Text:=FieldByName('HJ').AsString;
end;

TO SeaSky:
Thank you very very very much! You are a serious person on academic
issues. I admire you--I am serious!
The marks will go very soon, though I think 50 can't express my
feeling!
Thank you!

TO SeaSky:
Thank you very very very much! You are a serious person on academic
issues. I admire you--I am serious!
The marks will go very soon, though I think 50 can't express my
feeling!
Thank you!