如题,有一条 120行的SQL语句,如何快速的传给AdoQeury的SQL ?比如把
select bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)/365) age,
to_char(date_of_birth,'YYYY-MM-DD') csny,marital_status 转换为: s:=s+ 'select bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)' +
'/365) age, to_char(date_of_birth,''YYYY-MM-DD'') csny,marital_status '
这种格式。
如果语句只有几行到没什么,要是上百行怎么快速、准确的处理?首先排除loadformfile的办法,因为语句为了防止串改,是不能写在文本文件里的,求最佳办法!
//-------- zy_pat_masterselect bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)/365) age,
to_char(date_of_birth,'YYYY-MM-DD') csny,marital_status ,OCCUPATION_NAME,Nation,v.visit_id,v.charge_type,
to_char(v.admission_date_time,'YYYY-MM-DD') admission_date, dd1.dept_name,p.pat_condition_name ,dc.Diagnosis_code, n.Diagnosis_desc,
to_char(n.DIAGNOSIS_DATE,'YYYY-MM-DD') zdrq,to_char(v.Discharge_date_time,'YYYY-MM-DD') cyrq, dd2.dept_name , f.diagnosis_code,
f.diagnosis_desc,f.treat_result,
'' diagnosis_code1, '' diagnosis_desc1,''treat_result1,
'' diagnosis_code2, '' diagnosis_desc2,''treat_result2,
'' diagnosis_code3, '' diagnosis_desc3,''treat_result3,
'' diagnosis_code4, '' diagnosis_desc4,''treat_result4,
'' diagnosis_code5, '' diagnosis_desc5,''treat_result5,
'' diagnosis_code6, '' diagnosis_desc6,''treat_result6,
'' diagnosis_code7, '' diagnosis_desc7,''treat_result7,
'' yygrbm,'' yygrmc,'' zljg,'' blzd,'' blmc ,
operation_code,to_char(operating_date,'yyyy-mm-dd'),operation_desc ,HEAL,
'' operation_code2, '' operating_date2, '' operation_desc2 , '' HEAL2 ,
'' operation_code3, '' operating_date3, '' operation_desc3 , '' HEAL3 ,
'' operation_code4, '' operating_date4, '' operation_desc4 , '' HEAL4 ,
'' operation_code5, '' operating_date5, '' operation_desc5 , '' HEAL5 ,
EMER_TREAT_TIMES, ESC_EMER_TIMES , zs, chuangwei,huli,xiyao,zhongcheng,zhongcao,fangshe,huayan,shuyang,
shuxue,shoushu,jiancha,mazui,zhiliao,qita,
to_char(sysdate,'YYYY-MM-DD') tbrq
from pat_master_index m ,pat_visit v , OCCUPATION_DICT d ,diagnosis n , FINAL_CHIEF_DIAGNOSIS f ,dept_dict dd1 ,dept_dict dd2,
PAT_ADM_CONDITION_DICT p ,DIAGNOSTIC_CATEGORY dc ,OPERATION o ,
(
select patient_id ,visit_id,
sum(jiancha) jiancha,
sum(qita) qita,
sum(huayan) huayan,
sum(xiyao) xiyao,
sum(yinger) yinger,
sum(zhenliao) zhenliao,
sum(zhongyao) zhongyao,
sum(chuangwei) chuangwei,
sum(shuxue) shuxue,
sum(jiesheng) jiesheng,
sum(mazui) mazui,
sum(zhiliao) zhiliao,
sum(fangshe) fangshe,
sum(shoushu) shoushu,
sum(shuyang) shuyang,
sum(zhongcao) zhongcao,
sum(zhongcheng) zhongcheng,
sum(huli) huli,
sum(peichuang) peichuang
from
(
select med.patient_id,med.visit_id,
decode(fee_type,'检查',costs,0) jiancha,
decode(fee_type,'其他',costs,0) qita,
decode(fee_type,'化验',costs,0) huayan,
decode(fee_type,'西药',costs,0) xiyao,
decode(fee_type,'婴儿',costs,0) yinger,
decode(fee_type,'诊疗',costs,0) zhenliao,
decode(fee_type,'中药',costs,0) zhongyao,
decode(fee_type,'床位',costs,0) chuangwei,
decode(fee_type,'输血',costs,0) shuxue,
decode(fee_type,'接生',costs,0) jiesheng,
decode(fee_type,'麻醉',costs,0) mazui,
decode(fee_type,'治疗',costs,0) zhiliao,
decode(fee_type,'放射',costs,0) fangshe,
decode(fee_type,'手术',costs,0) shoushu,
decode(fee_type,'输氧',costs,0) shuyang,
decode(fee_type,'中草',costs,0) zhongcao,
decode(fee_type,'中成',costs,0) zhongcheng,
decode(fee_type,'护理',costs,0) huli,
decode(fee_type,'陪床',costs,0) peichuang
from medical_costs med
where (patient_id,visit_id) in
(select patient_id,visit_id from pat_visit where
discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and discharge_date_time < to_date('2012-07-01','YYYY-MM-DD'))
)
group by patient_id ,visit_id
) med2 ,
(
select patient_id,visit_id,sum(costs) zs from medical_costs
where (patient_id,visit_id) in
(select patient_id,visit_id from pat_visit where
discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and discharge_date_time < to_date('2012-07-01','YYYY-MM-DD'))
group by patient_id,visit_id
) med3
where m.patient_id=v.patient_id
and v.discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and v.discharge_date_time < to_date('2012-07-01','YYYY-MM-DD')
and bas_no is not nulland v.occupation=d.OCCUPATION_CODE(+)and v.patient_id=f.patient_id
and v.visit_id=f.visit_id
and v.dept_admission_to=dd1.dept_code
and v.dept_discharge_from =dd2.dept_code
and v.pat_adm_condition=p.pat_condition_codeand v.patient_id=n.patient_id
and v.visit_id=n.visit_id
and n.diagnosis_type=2 and n.diagnosis_no=1
and v.patient_id=dc.patient_id
and v.visit_id=dc.visit_id
and dc.diagnosis_type=2 and dc.diagnosis_no=1and v.patient_id=o.patient_id(+)
and v.visit_id=o.visit_id(+)
and (o.operation_no is null or operation_no<2) and v.patient_id=med2.patient_id(+)
and v.visit_id=med2.visit_id(+)and v.patient_id=med3.patient_id(+)
and v.visit_id=med3.visit_id(+);
select bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)/365) age,
to_char(date_of_birth,'YYYY-MM-DD') csny,marital_status 转换为: s:=s+ 'select bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)' +
'/365) age, to_char(date_of_birth,''YYYY-MM-DD'') csny,marital_status '
这种格式。
如果语句只有几行到没什么,要是上百行怎么快速、准确的处理?首先排除loadformfile的办法,因为语句为了防止串改,是不能写在文本文件里的,求最佳办法!
//-------- zy_pat_masterselect bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)/365) age,
to_char(date_of_birth,'YYYY-MM-DD') csny,marital_status ,OCCUPATION_NAME,Nation,v.visit_id,v.charge_type,
to_char(v.admission_date_time,'YYYY-MM-DD') admission_date, dd1.dept_name,p.pat_condition_name ,dc.Diagnosis_code, n.Diagnosis_desc,
to_char(n.DIAGNOSIS_DATE,'YYYY-MM-DD') zdrq,to_char(v.Discharge_date_time,'YYYY-MM-DD') cyrq, dd2.dept_name , f.diagnosis_code,
f.diagnosis_desc,f.treat_result,
'' diagnosis_code1, '' diagnosis_desc1,''treat_result1,
'' diagnosis_code2, '' diagnosis_desc2,''treat_result2,
'' diagnosis_code3, '' diagnosis_desc3,''treat_result3,
'' diagnosis_code4, '' diagnosis_desc4,''treat_result4,
'' diagnosis_code5, '' diagnosis_desc5,''treat_result5,
'' diagnosis_code6, '' diagnosis_desc6,''treat_result6,
'' diagnosis_code7, '' diagnosis_desc7,''treat_result7,
'' yygrbm,'' yygrmc,'' zljg,'' blzd,'' blmc ,
operation_code,to_char(operating_date,'yyyy-mm-dd'),operation_desc ,HEAL,
'' operation_code2, '' operating_date2, '' operation_desc2 , '' HEAL2 ,
'' operation_code3, '' operating_date3, '' operation_desc3 , '' HEAL3 ,
'' operation_code4, '' operating_date4, '' operation_desc4 , '' HEAL4 ,
'' operation_code5, '' operating_date5, '' operation_desc5 , '' HEAL5 ,
EMER_TREAT_TIMES, ESC_EMER_TIMES , zs, chuangwei,huli,xiyao,zhongcheng,zhongcao,fangshe,huayan,shuyang,
shuxue,shoushu,jiancha,mazui,zhiliao,qita,
to_char(sysdate,'YYYY-MM-DD') tbrq
from pat_master_index m ,pat_visit v , OCCUPATION_DICT d ,diagnosis n , FINAL_CHIEF_DIAGNOSIS f ,dept_dict dd1 ,dept_dict dd2,
PAT_ADM_CONDITION_DICT p ,DIAGNOSTIC_CATEGORY dc ,OPERATION o ,
(
select patient_id ,visit_id,
sum(jiancha) jiancha,
sum(qita) qita,
sum(huayan) huayan,
sum(xiyao) xiyao,
sum(yinger) yinger,
sum(zhenliao) zhenliao,
sum(zhongyao) zhongyao,
sum(chuangwei) chuangwei,
sum(shuxue) shuxue,
sum(jiesheng) jiesheng,
sum(mazui) mazui,
sum(zhiliao) zhiliao,
sum(fangshe) fangshe,
sum(shoushu) shoushu,
sum(shuyang) shuyang,
sum(zhongcao) zhongcao,
sum(zhongcheng) zhongcheng,
sum(huli) huli,
sum(peichuang) peichuang
from
(
select med.patient_id,med.visit_id,
decode(fee_type,'检查',costs,0) jiancha,
decode(fee_type,'其他',costs,0) qita,
decode(fee_type,'化验',costs,0) huayan,
decode(fee_type,'西药',costs,0) xiyao,
decode(fee_type,'婴儿',costs,0) yinger,
decode(fee_type,'诊疗',costs,0) zhenliao,
decode(fee_type,'中药',costs,0) zhongyao,
decode(fee_type,'床位',costs,0) chuangwei,
decode(fee_type,'输血',costs,0) shuxue,
decode(fee_type,'接生',costs,0) jiesheng,
decode(fee_type,'麻醉',costs,0) mazui,
decode(fee_type,'治疗',costs,0) zhiliao,
decode(fee_type,'放射',costs,0) fangshe,
decode(fee_type,'手术',costs,0) shoushu,
decode(fee_type,'输氧',costs,0) shuyang,
decode(fee_type,'中草',costs,0) zhongcao,
decode(fee_type,'中成',costs,0) zhongcheng,
decode(fee_type,'护理',costs,0) huli,
decode(fee_type,'陪床',costs,0) peichuang
from medical_costs med
where (patient_id,visit_id) in
(select patient_id,visit_id from pat_visit where
discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and discharge_date_time < to_date('2012-07-01','YYYY-MM-DD'))
)
group by patient_id ,visit_id
) med2 ,
(
select patient_id,visit_id,sum(costs) zs from medical_costs
where (patient_id,visit_id) in
(select patient_id,visit_id from pat_visit where
discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and discharge_date_time < to_date('2012-07-01','YYYY-MM-DD'))
group by patient_id,visit_id
) med3
where m.patient_id=v.patient_id
and v.discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and v.discharge_date_time < to_date('2012-07-01','YYYY-MM-DD')
and bas_no is not nulland v.occupation=d.OCCUPATION_CODE(+)and v.patient_id=f.patient_id
and v.visit_id=f.visit_id
and v.dept_admission_to=dd1.dept_code
and v.dept_discharge_from =dd2.dept_code
and v.pat_adm_condition=p.pat_condition_codeand v.patient_id=n.patient_id
and v.visit_id=n.visit_id
and n.diagnosis_type=2 and n.diagnosis_no=1
and v.patient_id=dc.patient_id
and v.visit_id=dc.visit_id
and dc.diagnosis_type=2 and dc.diagnosis_no=1and v.patient_id=o.patient_id(+)
and v.visit_id=o.visit_id(+)
and (o.operation_no is null or operation_no<2) and v.patient_id=med2.patient_id(+)
and v.visit_id=med2.visit_id(+)and v.patient_id=med3.patient_id(+)
and v.visit_id=med3.visit_id(+);
unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls;type
TForm1 = class(TForm)
Memo1: TMemo;
Button1: TButton;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}uses Clipbrd;procedure TForm1.Button1Click(Sender: TObject);
var
S: string;
begin
S := StringReplace(Memo1.Text, '''', '''''', [rfReplaceAll]);//Memo1中放SQL语句
S := StringReplace(S, sLineBreak, ''' + sLineBreak +' + sLineBreak + ' ''', [rfReplaceAll]);
S := 'S := ''' + S + ''';'; Clipboard.SetTextBuf(PChar(S));
ShowMessage('代码已复制到剪贴板!');
end;procedure TForm1.FormCreate(Sender: TObject);
begin
Memo1.Clear;
Memo1.ScrollBars := ssBoth;
end;end.
这里有下载:http://bbs.cnpack.org/forumdisplay.php?fid=8
,装好后,只要你选取一段,然后右键你会找到专家包给你提供的辅助操作-转为字符串