ขอคำสัง update และเปลี่ยน format field ด้วยครับ

[yord]

คือมีข้อมูล เลขที่บัตรประชาชนใน table ptcardno โดย format 0-0000-00000-00-0
แล้วผมจะ update มาใส่ใน table patient โดย format 0000000000000 จะเขียนคำสั่งอย่างไรครับ

[naj]

ถ้าไม่รีบ รอสักครู่ครับ 

[yord]

ขอบคุณครับ จะรอครับ อ.

[manoi]

update patient set cid  = (select replace(cardno,'-','') from ptcardno
where ptcardno.hn = patient.hn and ptcardno.cardtype='01' and ptcardno.cardno not like '0-0000%')
where hn in (select hn from ptcardno where ptcardno.hn = patient.hn
and ptcardno.cardtype='01' and ptcardno.cardno not like '0-0000%' ) and (cid = '' or cid is null)



คำเตือน ใช้เวลานาน server อาจะ hang ได้

[yord]

ขอบคุณครับ อ.

[naj]

ขอโทษที่ตอบช้า กำลังทดสอบอยู่ แต่อย่างที่ อ.mn เตือนนะรับว่าจะมีผลทำให้ระบบค้างได้ถ้ามีข้อมูลมากๆ
มีสองส่วนครับ
ส่วนแรก ให้ไปวางในส่วนที่เป็น source

โค๊ด: Delphi

1. unit Unit1;
2.  
3. interface
4.  
5. uses
6.   Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
7.   Dialogs, ExtCtrls, StdCtrls, DB, DBClient, Grids, DBGrids, ComCtrls;
8.  
9. type
10.   TForm1 = class(TForm)
11.     Panel1: TPanel;
12.     Panel2: TPanel;
13.     Panel3: TPanel;
14.     DBGrid1: TDBGrid;
15.     cds: TClientDataSet;
16.     ds: TDataSource;
17.     Button1: TButton;
18.     Button2: TButton;
19.     Button3: TButton;
20.     Label1: TLabel;
21.     cxDateEdit1: TcxDateEdit;
22.     procedure Button1Click(Sender: TObject);
23.     procedure Button2Click(Sender: TObject);
24.     procedure Button3Click(Sender: TObject);
25.   private
26.     { Private declarations }
27.   public
28.     { Public declarations }
29.   end;
30.  
31. var
32.   Form1: TForm1;
33.  
34. implementation
35.  
36. {$R *.dfm}
37.  
38. procedure TForm1.Button1Click(Sender: TObject);
39. var tc, pt: tclientdataset; s: string;
40. begin
41.   cds.data := hosxp_getdataset('select hn,count(*) as cardno_count from ptcardno where cardtype="01" and cardno not like"" group by hn');
42.   tc := tclientdataset.create(nil);
43.   pt := tclientdataset.create(nil);
44.   cds.first;
45.   while not cds.eof do
46.   begin
47.     tc.data := HOSxP_GetDataset('select cardno from ptcardno where hn = "' + cds.fieldbyname('hn').asstring + '" and cardtype="01" and cardno not like"" ');
48.     s := '';
49.     while not tc.eof do
50.     begin
51.       if s = '' then s := tc.fieldbyname('cardno').asstring else
52.         s := s + ':' + tc.fieldbyname('cardno').asstring;
53.       tc.next;
54.     end;
55.  
56.     if trim(s) <> '' then
57.     begin
58.       pt.data := HOSxP_Getdataset('select * from patient where hn = "' + cds.fieldbyname('hn').asstring + '"');
59.       if pt.recordcount > 0 then
60.       begin
61.         pt.edit;
62.         pt.fieldbyname('cid').asstring := replacestr(s,'-','');
63.         pt.post;
64.  
65.         if pt.changecount > 0 then
66.         begin
67.           showdebugtext('Update CID hn : '+cds.fieldbyname('hn').asstring+' -- > Old CID = '+pt.fieldbyname('cid').asstring+'-----> NEW CID = '+replacestr(s,'-',''));
68.           hosxp_updatedelta(pt.delta, 'select * from patient where hn = "' + cds.fieldbyname('hn').asstring + '"');
69.         end;
70.       end;
71.  
72.     end;
73.  
74.     cds.next;
75.   end;
76.   tc.free;
77.   pt.free;
78.  
79.   showmessage('Update Done.');
80.  
81. end;
82.  
83. procedure TForm1.Button2Click(Sender: TObject);
84. begin
85.   fcds.data := cds.data;
86.   ExportDatasetToExcel('test.xls');
87. end;
88.  
89. procedure TForm1.Button3Click(Sender: TObject);
90. begin
91.   fcds.data := cds.data;
92.   CreateDatasetReport('HOSxP Report');
93.  
94. end;
95.  
96. end.

ส่วนที่สองให้ไปวางใน DFM

โค๊ด: Delphi

1. object Form1: TForm1
2.   Left = 0
3.   Top = 0
4.   Caption = '[ HOSxP CID Update  ]'
5.   ClientHeight = 511
6.   ClientWidth = 597
7.   Color = clBtnFace
8.   Font.Charset = DEFAULT_CHARSET
9.   Font.Color = clWindowText
10.   Font.Height = -11
11.   Font.Name = 'Tahoma'
12.   Font.Style = []
13.   OldCreateOrder = False
14.   Position = poMainFormCenter
15.   PixelsPerInch = 96
16.   TextHeight = 13
17.   object Panel1: TPanel
18.     Left = 0
19.     Top = 0
20.     Width = 597
21.     Height = 40
22.     Align = alTop
23.     Caption = 'HOSxP CID Update'
24.     Font.Charset = DEFAULT_CHARSET
25.     Font.Color = clWindowText
26.     Font.Height = -13
27.     Font.Name = 'Tahoma'
28.     Font.Style = [fsBold]
29.     ParentFont = False
30.     TabOrder = 0
31.   end
32.   object Panel2: TPanel
33.     Left = 0
34.     Top = 470
35.     Width = 597
36.     Height = 41
37.     Align = alBottom
38.     BevelInner = bvRaised
39.     BevelOuter = bvLowered
40.     TabOrder = 1
41.   end
42.   object Panel3: TPanel
43.     Left = 0
44.     Top = 40
45.     Width = 597
46.     Height = 48
47.     Align = alTop
48.     BevelInner = bvRaised
49.     BevelOuter = bvLowered
50.     TabOrder = 2
51.     object Label1: TLabel
52.       Left = 15
53.       Top = 18
54.       Width = 59
55.       Height = 13
56.       Caption = 'Patient View'
57.     end
58.     object Button1: TButton
59.       Left = 286
60.       Top = 12
61.       Width = 125
62.       Height = 25
63.       Caption = 'Update CID'
64.       TabOrder = 0
65.       OnClick = Button1Click
66.     end
67.     object Button2: TButton
68.       Left = 372
69.       Top = 12
70.       Width = 75
71.       Height = 25
72.       Caption = 'Excel Export'
73.       TabOrder = 1
74.       OnClick = Button2Click
75.       visible = false
76.     end
77.     object Button3: TButton
78.       Left = 472
79.       Top = 12
80.       Width = 75
81.       Height = 25
82.       Caption = 'report'
83.       TabOrder = 1
84.       OnClick = Button3Click
85.       visible = false
86.     end
87.     object cxDateEdit1: TcxDateEdit
88.       Left = 84
89.       Top = 15
90.       TabOrder = 2
91.       Width = 154
92.       visible = false
93.     end
94.   end
95.   object DBGrid1: TDBGrid
96.     Left = 0
97.     Top = 88
98.     Width = 597
99.     Height = 382
100.     Align = alClient
101.     DataSource = ds
102.     TabOrder = 3
103.     TitleFont.Charset = DEFAULT_CHARSET
104.     TitleFont.Color = clWindowText
105.     TitleFont.Height = -11
106.     TitleFont.Name = 'Tahoma'
107.     TitleFont.Style = []
108.   end
109.   object cds: TClientDataSet
110.     Aggregates = <>
111.     Params = <>
112.     Left = 39
113.     Top = 6
114.   end
115.   object ds: TDataSource
116.     DataSet = cds
117.     Left = 78
118.     Top = 6
119.   end
120. end

[SrWooD]

สุดยอดเลยครับ เดี๋ยวขอทดสอบด้วยคนครับ
ขอบคุณครับ