我有一条长的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里面应该怎样写啊
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里面应该怎样写啊
sl := sl + 'ELECT *, (discountamount - CONVERT(int, payed_account))';
sl := sl + ' AS left_money FROM';
......
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;