有关长sql语句的写法,请高手指点!!!

Question

我有一条长的sql语句,想把它插入到access数据库中,代码如下:
var s1:string;
s1:='SELECT  *, (discountamount - CONVERT(int, payed_account)) AS left_money
FROM   (SELECT  ordergroup.groupno,customer.balancename,customer.contact1,              
customer.customerid,customer.code, ordergroup.groupid, customer.companyname,                             
customer.tel1,customer.address,customer.fax1,invoice.invoicecode, order_account.order_price, invoice.discountamount, invoice.invoicedate,               payed_account = (CASE WHEN payed_invoice.payed_account IS NULL THEN 0 ELSE        payed_invoice.payed_account END)                                                        FROM       ordergroup      LEFT JOIN                                                      
 (SELECT         SUM(price) AS order_price, groupid
                                                            FROM              orderform
                                                            WHERE          deleteflag = 0 AND price IS NOT NULL
                                                            GROUP BY   groupid) AS order_account ON 
                                                      order_account.groupid = ordergroup.groupid LEFT JOIN
                                                      customer ON ordergroup.customerid = customer.customerid, 
                                                      invoice LEFT JOIN                                                       (SELECT         SUM(paymentamount) AS   payed_account,  invoiceid                                                             FROM              payment
                                                            WHERE          paymentamount IS NOT NULL
                                                            GROUP BY   invoiceid) AS payed_invoice ON 
                                                           invoice.invoiceid = payed_invoice.invoiceid
                           WHERE          ordergroup.groupid = invoice.groupid AND 
                                                      invoice.deleteflag = 0 AND ordergroup.deleteflag = 0) AS a
                           WHERE                                discountamount - payed_account > 0
';
reportqry.Close;
reportqry.SQL.Clear;
reportqry.SQL.Add('insert into report (mainsql,detailsql) values (''s1'')');      
reportqry.Prepared;
reportqry.ExecSQL;
现在关键是s1那里报错,请问高手们象这种长的sql语句在delphi里面应该怎样写啊

Answer 1

表里你选了两个字段mainsql,detailsql,而values里只填了一个字段,当然报错了!

Answer 2

对了，你自己的insert into 语句错了，

Answer 3

insert into report (mainsql,detailsql)有两个字段

Answer 4

还有,delphi中一句不能超过255个字符所以那个赋值应改为sl := '';
sl := sl + 'ELECT  *, (discountamount - CONVERT(int, payed_account))';
sl := sl + ' AS left_money FROM';
......

Answer 5

早晨有急事出去了,落了一个事,delphi里面声明的string变量也不能超过255个字符所以应该这么写
var sl:TStringlist;
begin
  sl := TStringlist.create
  try
    sl.add('SELECT  *, (discountamount - CONVERT(int, payed_account)');
    sl.add(' AS left_money FROM');
    ......
    with reportqry do
      begin
        close;
        sql.text:='insert into report (mainsql,detailsql) values (:mainsql,:detailsql)';
        Parameters.ParamValues['mainsql']:=sl.text;
        Parameters.ParamValues['detailsql']:='';
        ExecSQL;
      end;
  finally
    sl.free;
  end;
end;