我在做一个HIS系统,有个关于病人费用明细单的查询出了问题。
我在delphi6中的语句是:
With Dm_charge.Aquery_faremedicine do
Begin
close;sql.Clear ;
sql.Add('SELECT IPMAKEPRICE.RECIPECODE,IPMAKEPRICE.ITEMNAME,IPMAKEPRICE.DG_SP_DESC,DRUGBASE.DG_PACKUNIT,');
sql.Add('IPMAKEPRICE.ITEMPRICE,((IPMAKEPRICE.BASEUNITQUAN+IPMAKEPRICE.PACKUNITQUAN*IPMAKEPRICE.DG_FACTOR-ipmakeprice.cancelbasequan-ipmakeprice.cancelpackquan*ipmakeprice.dg_factor))/IPMAKEPRICE.DG_FACTOR AS QUAN,');
SQL.Add('(IPMAKEPRICE.ITEMMONEY-IPMAKEPRICE.CANCELMONEY)AS CHAMONEY,IPCHARGE.CHARGEDATE,HOSPEMPLOYEE.HO_EMPLOYNAME,IPMAKEPRICE.RECIPELIST');
SQL.Add('FROM IPMAKEPRICE INNER JOIN IPCHARGE ON(IPCHARGE.ID_PATIENT=IPMAKEPRICE.ID_PATIENT AND IPCHARGE.MACHINECODE=');
SQL.Add('IPMAKEPRICE.MACHINECODE AND IPCHARGE.RECIPECODE=IPMAKEPRICE.RECIPECODE)');
SQL.Add('JOIN DRUGBASE ON DRUGBASE.DG_CODE=IPMAKEPRICE.ITEMCODE INNER JOIN HOSPEMPLOYEE ON ');
SQL.Add('IPCHARGE.CHARGEOPERATOR=HOSPEMPLOYEE.HO_EMPLOYCODE');
SQL.Add('WHERE IPCHARGE.ABORTFLAG='+QUOTEDSTR('0')+' AND IPMAKEPRICE.ITEMTYPE='+QUOTEDSTR('1'));
SQL.ADD('AND IPMAKEPRICE.CANCELFLAG='+QUOTEDSTR('0'));
SQL.ADD('AND IPCHARGE.ID_PATIENT=:IDPATIENT1 AND IPCHARGE.MACHINECODE=:MACHINECODE1'); SQL.ADD('AND IPCHARGE.CHARGEDATE>=:DATE1 AND IPCHARGE.CHARGEDATE<=:DATE2');
SQL.Add('UNION ');
sql.Add('SELECT IPMAKEPRICE.RECIPECODE,IPMAKEPRICE.ITEMNAME,ipmakeprice.DG_SP_DESC,DRUGDICTIONARY2.DG_DOSEUNIT,');
sql.Add('IPMAKEPRICE.ITEMPRICE,(IPMAKEPRICE.BASEUNITQUAN*ipmakeprice.usemulti-ipmakeprice.cancelbasequan*ipmakeprice.usemulti)AS QUAN,');
SQL.Add('(IPMAKEPRICE.ITEMPRICE*IPMAKEPRICE.BASEUNITQUAN*IPMAKEPRICE.USEMULTI-IPMAKEPRICE.CANCELMONEY)AS CHAMONEY,IPCHARGE.CHARGEDATE,HOSPEMPLOYEE.HO_EMPLOYNAME,IPMAKEPRICE.RECIPELIST');
SQL.Add('FROM IPMAKEPRICE INNER JOIN IPCHARGE ON(IPCHARGE.ID_PATIENT=IPMAKEPRICE.ID_PATIENT AND IPCHARGE.MACHINECODE=');
SQL.Add('IPMAKEPRICE.MACHINECODE AND IPCHARGE.RECIPECODE=IPMAKEPRICE.RECIPECODE)');
SQL.Add('JOIN DRUGDICTIONARY2 ON DRUGDICTIONARY2.DG_dicCODE=IPMAKEPRICE.ITEMCODE INNER JOIN HOSPEMPLOYEE ON ');
SQL.Add('IPCHARGE.CHARGEOPERATOR=HOSPEMPLOYEE.HO_EMPLOYCODE');
SQL.Add('WHERE IPCHARGE.ABORTFLAG='+QUOTEDSTR('0')+' AND IPMAKEPRICE.ITEMTYPE='+QUOTEDSTR('2'));
SQL.ADD('AND IPMAKEPRICE.CANCELFLAG='+QUOTEDSTR('0'));
SQL.ADD('AND IPCHARGE.ID_PATIENT=:IDPATIENT2 AND IPCHARGE.MACHINECODE=:MACHINECODE2'); SQL.ADD('AND IPCHARGE.CHARGEDATE>=:DATE3 AND IPCHARGE.CHARGEDATE<=:DATE4');
parameters.ParamByName('IDPATIENT1').Value :=str_idpatient;
parameters.ParamByName('machinecode1').Value:=str_machinecode;
parameters.ParamByName('date1').Value:=datetostr(datetimepicker1.Date)+' 00:00:00'; parameters.ParamByName('date2').Value:=datetostr(datetimepicker2.Date)+' 00:00:00'; parameters.ParamByName('IDPATIENT2').Value :=str_idpatient;
parameters.ParamByName('machinecode2').Value:=str_machinecode;
parameters.ParamByName('date3').Value:=datetostr(datetimepicker1.Date)+' 00:00:00'; parameters.ParamByName('date4').Value:=datetostr(datetimepicker2.Date)+' 00:00:00'; open;
end; 但是编译运行是出现了错误
//出错信息提示
Projiect P_charge.exe raised exception class EAccessViolation with message 'Access violation
at address 78010513 in module 'msvcrt.dll'.write of address 00D700D3'.Process stopped. Use Step or Run to continue.我在sql2000查询分析器里面测试了这段sql语句,是可以查询成功的。
Sql2000中的语句:
SELECT IPMAKEPRICE.RECIPECODE,IPMAKEPRICE.ITEMNAME,IPMAKEPRICE.DG_SP_DESC,DRUGBASE.DG_PACKUNIT, IPMAKEPRICE.ITEMPRICE,((IPMAKEPRICE.BASEUNITQUAN+IPMAKEPRICE.PACKUNITQUAN*IPMAKEPRICE.DG_FACTOR-ipmakeprice.cancelbasequan-ipmakeprice.cancelpackquan*ipmakeprice.dg_factor))/IPMAKEPRICE.DG_FACTOR AS QUAN,(IPMAKEPRICE.ITEMMONEY-IPMAKEPRICE.CANCELMONEY)AS CHAMONEY,IPCHARGE.CHARGEDATE,HOSPEMPLOYEE.HO_EMPLOYNAME,IPMAKEPRICE.RECIPELIST
FROM IPMAKEPRICE INNER JOIN IPCHARGE ON(IPCHARGE.ID_PATIENT=IPMAKEPRICE.ID_PATIENT AND IPCHARGE.MACHINECODE=IPMAKEPRICE.MACHINECODE AND IPCHARGE.RECIPECODE=IPMAKEPRICE.RECIPECODE) JOIN DRUGBASE ON DRUGBASE.DG_CODE=IPMAKEPRICE.ITEMCODE
INNER JOIN HOSPEMPLOYEE ON IPCHARGE.CHARGEOPERATOR=HOSPEMPLOYEE.HO_EMPLOYCODE
WHERE IPCHARGE.ABORTFLAG=0 AND IPMAKEPRICE.ITEMTYPE=1 AND IPMAKEPRICE.CANCELFLAG=0 AND IPCHARGE.ID_PATIENT='1200305000001' AND IPCHARGE.MACHINECODE='13' AND IPCHARGE.CHARGEDATE>='2003-05-26 22:34:12' AND IPCHARGE.CHARGEDATE<='2003-06-26 22:34:12'
UNION
SELECT IPMAKEPRICE.RECIPECODE,IPMAKEPRICE.ITEMNAME,ipmakeprice.DG_SP_DESC,DRUGDICTIONARY2.DG_DOSEUNIT,IPMAKEPRICE.ITEMPRICE,(IPMAKEPRICE.BASEUNITQUAN*ipmakeprice.usemulti-ipmakeprice.cancelbasequan*ipmakeprice.usemulti)AS QUAN,(IPMAKEPRICE.ITEMPRICE*IPMAKEPRICE.BASEUNITQUAN*IPMAKEPRICE.USEMULTI-IPMAKEPRICE.CANCELMONEY)AS CHAMONEY,IPCHARGE.CHARGEDATE,HOSPEMPLOYEE.HO_EMPLOYNAME,IPMAKEPRICE.RECIPELIST
FROM IPMAKEPRICE INNER JOIN IPCHARGE ON(IPCHARGE.ID_PATIENT=IPMAKEPRICE.ID_PATIENT AND IPCHARGE.MACHINECODE=IPMAKEPRICE.MACHINECODE AND IPCHARGE.RECIPECODE=IPMAKEPRICE.RECIPECODE)
JOIN DRUGDICTIONARY2 ON DRUGDICTIONARY2.DG_dicCODE=IPMAKEPRICE.ITEMCODE
INNER JOIN HOSPEMPLOYEE ON IPCHARGE.CHARGEOPERATOR=HOSPEMPLOYEE.HO_EMPLOYCODE
WHERE IPCHARGE.ABORTFLAG='0' AND IPMAKEPRICE.ITEMTYPE='2'AND IPMAKEPRICE.CANCELFLAG='0' AND IPCHARGE.ID_PATIENT='1200305000001' AND IPCHARGE.MACHINECODE='13' AND IPCHARGE.CHARGEDATE>='2003-05-26 22:34:12' AND IPCHARGE.CHARGEDATE<='2003-06-26 22:34:12'所以不知道是sql2000的问题还是delphi6不支持UNION语句,到底问题出在了哪里?
非常感谢!
我在delphi6中的语句是:
With Dm_charge.Aquery_faremedicine do
Begin
close;sql.Clear ;
sql.Add('SELECT IPMAKEPRICE.RECIPECODE,IPMAKEPRICE.ITEMNAME,IPMAKEPRICE.DG_SP_DESC,DRUGBASE.DG_PACKUNIT,');
sql.Add('IPMAKEPRICE.ITEMPRICE,((IPMAKEPRICE.BASEUNITQUAN+IPMAKEPRICE.PACKUNITQUAN*IPMAKEPRICE.DG_FACTOR-ipmakeprice.cancelbasequan-ipmakeprice.cancelpackquan*ipmakeprice.dg_factor))/IPMAKEPRICE.DG_FACTOR AS QUAN,');
SQL.Add('(IPMAKEPRICE.ITEMMONEY-IPMAKEPRICE.CANCELMONEY)AS CHAMONEY,IPCHARGE.CHARGEDATE,HOSPEMPLOYEE.HO_EMPLOYNAME,IPMAKEPRICE.RECIPELIST');
SQL.Add('FROM IPMAKEPRICE INNER JOIN IPCHARGE ON(IPCHARGE.ID_PATIENT=IPMAKEPRICE.ID_PATIENT AND IPCHARGE.MACHINECODE=');
SQL.Add('IPMAKEPRICE.MACHINECODE AND IPCHARGE.RECIPECODE=IPMAKEPRICE.RECIPECODE)');
SQL.Add('JOIN DRUGBASE ON DRUGBASE.DG_CODE=IPMAKEPRICE.ITEMCODE INNER JOIN HOSPEMPLOYEE ON ');
SQL.Add('IPCHARGE.CHARGEOPERATOR=HOSPEMPLOYEE.HO_EMPLOYCODE');
SQL.Add('WHERE IPCHARGE.ABORTFLAG='+QUOTEDSTR('0')+' AND IPMAKEPRICE.ITEMTYPE='+QUOTEDSTR('1'));
SQL.ADD('AND IPMAKEPRICE.CANCELFLAG='+QUOTEDSTR('0'));
SQL.ADD('AND IPCHARGE.ID_PATIENT=:IDPATIENT1 AND IPCHARGE.MACHINECODE=:MACHINECODE1'); SQL.ADD('AND IPCHARGE.CHARGEDATE>=:DATE1 AND IPCHARGE.CHARGEDATE<=:DATE2');
SQL.Add('UNION ');
sql.Add('SELECT IPMAKEPRICE.RECIPECODE,IPMAKEPRICE.ITEMNAME,ipmakeprice.DG_SP_DESC,DRUGDICTIONARY2.DG_DOSEUNIT,');
sql.Add('IPMAKEPRICE.ITEMPRICE,(IPMAKEPRICE.BASEUNITQUAN*ipmakeprice.usemulti-ipmakeprice.cancelbasequan*ipmakeprice.usemulti)AS QUAN,');
SQL.Add('(IPMAKEPRICE.ITEMPRICE*IPMAKEPRICE.BASEUNITQUAN*IPMAKEPRICE.USEMULTI-IPMAKEPRICE.CANCELMONEY)AS CHAMONEY,IPCHARGE.CHARGEDATE,HOSPEMPLOYEE.HO_EMPLOYNAME,IPMAKEPRICE.RECIPELIST');
SQL.Add('FROM IPMAKEPRICE INNER JOIN IPCHARGE ON(IPCHARGE.ID_PATIENT=IPMAKEPRICE.ID_PATIENT AND IPCHARGE.MACHINECODE=');
SQL.Add('IPMAKEPRICE.MACHINECODE AND IPCHARGE.RECIPECODE=IPMAKEPRICE.RECIPECODE)');
SQL.Add('JOIN DRUGDICTIONARY2 ON DRUGDICTIONARY2.DG_dicCODE=IPMAKEPRICE.ITEMCODE INNER JOIN HOSPEMPLOYEE ON ');
SQL.Add('IPCHARGE.CHARGEOPERATOR=HOSPEMPLOYEE.HO_EMPLOYCODE');
SQL.Add('WHERE IPCHARGE.ABORTFLAG='+QUOTEDSTR('0')+' AND IPMAKEPRICE.ITEMTYPE='+QUOTEDSTR('2'));
SQL.ADD('AND IPMAKEPRICE.CANCELFLAG='+QUOTEDSTR('0'));
SQL.ADD('AND IPCHARGE.ID_PATIENT=:IDPATIENT2 AND IPCHARGE.MACHINECODE=:MACHINECODE2'); SQL.ADD('AND IPCHARGE.CHARGEDATE>=:DATE3 AND IPCHARGE.CHARGEDATE<=:DATE4');
parameters.ParamByName('IDPATIENT1').Value :=str_idpatient;
parameters.ParamByName('machinecode1').Value:=str_machinecode;
parameters.ParamByName('date1').Value:=datetostr(datetimepicker1.Date)+' 00:00:00'; parameters.ParamByName('date2').Value:=datetostr(datetimepicker2.Date)+' 00:00:00'; parameters.ParamByName('IDPATIENT2').Value :=str_idpatient;
parameters.ParamByName('machinecode2').Value:=str_machinecode;
parameters.ParamByName('date3').Value:=datetostr(datetimepicker1.Date)+' 00:00:00'; parameters.ParamByName('date4').Value:=datetostr(datetimepicker2.Date)+' 00:00:00'; open;
end; 但是编译运行是出现了错误
//出错信息提示
Projiect P_charge.exe raised exception class EAccessViolation with message 'Access violation
at address 78010513 in module 'msvcrt.dll'.write of address 00D700D3'.Process stopped. Use Step or Run to continue.我在sql2000查询分析器里面测试了这段sql语句,是可以查询成功的。
Sql2000中的语句:
SELECT IPMAKEPRICE.RECIPECODE,IPMAKEPRICE.ITEMNAME,IPMAKEPRICE.DG_SP_DESC,DRUGBASE.DG_PACKUNIT, IPMAKEPRICE.ITEMPRICE,((IPMAKEPRICE.BASEUNITQUAN+IPMAKEPRICE.PACKUNITQUAN*IPMAKEPRICE.DG_FACTOR-ipmakeprice.cancelbasequan-ipmakeprice.cancelpackquan*ipmakeprice.dg_factor))/IPMAKEPRICE.DG_FACTOR AS QUAN,(IPMAKEPRICE.ITEMMONEY-IPMAKEPRICE.CANCELMONEY)AS CHAMONEY,IPCHARGE.CHARGEDATE,HOSPEMPLOYEE.HO_EMPLOYNAME,IPMAKEPRICE.RECIPELIST
FROM IPMAKEPRICE INNER JOIN IPCHARGE ON(IPCHARGE.ID_PATIENT=IPMAKEPRICE.ID_PATIENT AND IPCHARGE.MACHINECODE=IPMAKEPRICE.MACHINECODE AND IPCHARGE.RECIPECODE=IPMAKEPRICE.RECIPECODE) JOIN DRUGBASE ON DRUGBASE.DG_CODE=IPMAKEPRICE.ITEMCODE
INNER JOIN HOSPEMPLOYEE ON IPCHARGE.CHARGEOPERATOR=HOSPEMPLOYEE.HO_EMPLOYCODE
WHERE IPCHARGE.ABORTFLAG=0 AND IPMAKEPRICE.ITEMTYPE=1 AND IPMAKEPRICE.CANCELFLAG=0 AND IPCHARGE.ID_PATIENT='1200305000001' AND IPCHARGE.MACHINECODE='13' AND IPCHARGE.CHARGEDATE>='2003-05-26 22:34:12' AND IPCHARGE.CHARGEDATE<='2003-06-26 22:34:12'
UNION
SELECT IPMAKEPRICE.RECIPECODE,IPMAKEPRICE.ITEMNAME,ipmakeprice.DG_SP_DESC,DRUGDICTIONARY2.DG_DOSEUNIT,IPMAKEPRICE.ITEMPRICE,(IPMAKEPRICE.BASEUNITQUAN*ipmakeprice.usemulti-ipmakeprice.cancelbasequan*ipmakeprice.usemulti)AS QUAN,(IPMAKEPRICE.ITEMPRICE*IPMAKEPRICE.BASEUNITQUAN*IPMAKEPRICE.USEMULTI-IPMAKEPRICE.CANCELMONEY)AS CHAMONEY,IPCHARGE.CHARGEDATE,HOSPEMPLOYEE.HO_EMPLOYNAME,IPMAKEPRICE.RECIPELIST
FROM IPMAKEPRICE INNER JOIN IPCHARGE ON(IPCHARGE.ID_PATIENT=IPMAKEPRICE.ID_PATIENT AND IPCHARGE.MACHINECODE=IPMAKEPRICE.MACHINECODE AND IPCHARGE.RECIPECODE=IPMAKEPRICE.RECIPECODE)
JOIN DRUGDICTIONARY2 ON DRUGDICTIONARY2.DG_dicCODE=IPMAKEPRICE.ITEMCODE
INNER JOIN HOSPEMPLOYEE ON IPCHARGE.CHARGEOPERATOR=HOSPEMPLOYEE.HO_EMPLOYCODE
WHERE IPCHARGE.ABORTFLAG='0' AND IPMAKEPRICE.ITEMTYPE='2'AND IPMAKEPRICE.CANCELFLAG='0' AND IPCHARGE.ID_PATIENT='1200305000001' AND IPCHARGE.MACHINECODE='13' AND IPCHARGE.CHARGEDATE>='2003-05-26 22:34:12' AND IPCHARGE.CHARGEDATE<='2003-06-26 22:34:12'所以不知道是sql2000的问题还是delphi6不支持UNION语句,到底问题出在了哪里?
非常感谢!
解决方案 »
- 特殊功能,实现方法!
- 小妹求助各位大哥大姐:RAVE中改变页边距后怎么没有任何反应啊???急死了
- 请问mxOutlookbar控件的最新版是多少啊,哪儿能下载呀?
- 下雨了,凉快,回答我个问题吧!:)问题2
- 进程A和被注入的进程B的DLL之间 如何通过自定义消息进行通讯
- 如何把已经学好的程序变成安装的文件?
- 屏幕显示跟自己同样大小的固定窗体的时候,怎么达到最大化效果?
- TDBGrid的fields和columns 有什么区别?
- RBuilder 中,怎么把报表(包括所有数据)存为其它文件,以后可以不需要数据库也能用?
- XMLDocument的奇怪问题,高手请进
- Table出错提示
- 求教SQL语句!
打个断点看看什么地方出错
可以肯定的就是不存在什么不支持union语句的事既然能在sql2000查询分析器里面查询出你想要的结果,建议你用存储过程你为那些日期参数附值的时候,一定要用datetostr()函数吗?那些字段是日期型还是字符型?是日期型的话那参数附值就错了
听 hnhb(不死鸟) 的用存储过程吧。
它只是把SQL传给数据库罢了
你可以在执行SQL之前把它显示出来showmessage(sql.text),或另存为文本文件sql.savetofile('t.txt');