SQL语句在DELPHI不能通过? ( 积分: 100 )

  • 主题发起人 han--bing
  • 开始时间
H

han--bing

Unregistered / Unconfirmed
GUEST, unregistred user!
SELECT aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, SUM(aa.kcnum) AS kcnum, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind
FROM (
SELECT deptno, cpcode, cpname, isnull(spectype,'')as spectype, unit, 0 AS workkind, SUM(storenum) AS kcnum, 1 AS inflag,Null as jobprg
FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind >= 0 (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
UNION
SELECT deptno, cpcode, cpname, isnull(spectype,'')as spectype, unit, 0 AS workkind, SUM(storenum * - 1) AS kcnum, - 1 AS inflag,null as jobprg FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind < 0 AND (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
) aa
LEFT OUTER JOIN
dbo.pub_dpt bb ON aa.deptno = bb.pcode
inner join cp_info cc on aa.cpcode=cc.cpcode
GROUP BY aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind

在SQL分析器可以通过,在DELPHI怎么改写啊?
 
SELECT aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, SUM(aa.kcnum) AS kcnum, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind
FROM (
SELECT deptno, cpcode, cpname, isnull(spectype,'' [red][這里應該是''''][/red] )as spectype, unit, 0 AS workkind, SUM(storenum) AS kcnum, 1 AS inflag,Null as jobprg
FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind >= 0 (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
UNION
SELECT deptno, cpcode, cpname, isnull(spectype,'')as spectype, unit, 0 AS workkind, SUM(storenum * - 1) AS kcnum, - 1 AS inflag,null as jobprg FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind < 0 AND (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
) aa
LEFT OUTER JOIN
dbo.pub_dpt bb ON aa.deptno = bb.pcode
inner join cp_info cc on aa.cpcode=cc.cpcode
GROUP BY aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind
 
SELECT aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, SUM(aa.kcnum) AS kcnum, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind
FROM (
SELECT deptno, cpcode, cpname, isnull(spectype,'' [這里應該是''''])as spectype, unit, 0 AS workkind, SUM(storenum) AS kcnum, 1 AS inflag,Null as jobprg
FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind >= 0 (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
UNION
SELECT deptno, cpcode, cpname, isnull(spectype,'' [這里應該是''''] )as spectype, unit, 0 AS workkind, SUM(storenum * - 1) AS kcnum, - 1 AS inflag,null as jobprg FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind < 0 AND (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
) aa
LEFT OUTER JOIN
dbo.pub_dpt bb ON aa.deptno = bb.pcode
inner join cp_info cc on aa.cpcode=cc.cpcode
GROUP BY aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind


兩處的 isnull(spectype,'') [這里應該是''''] 改過來就可以了吧
 
不行,自己已更改存储器,问题解决,谢谢!
 
視圖也行。
主要是你的引號使用的問題。
 
在DELPHI SQL中 '号 用''''来表示
SQL句语中所有有'号的都要改字:比如
select * from table where ZD='123' 在查询器中
delphi 中 Commandtext := 'select * from table where ZD=' + '''' +'123' + ''''
 
delphi世界qq群:23981160
欢迎喜欢d的朋友来
 
顶部