关于Oracle9 里面的SQL写法 |
尚未結案
|
liangzai
初階會員 發表:77 回覆:91 積分:33 註冊:2004-08-13 發送簡訊給我 |
请问,我现在需要从几个相关联的数据表中提取数据,SQL写法如下:
dm.SQLQuery7.SQL.Text:='select a.*,b.model,unitprice,brand,code,c.voucherno,code,orderqty';
dm.SQLQuery7.SQL.Append('from EMGT.HR_MASTER a,EMGT.MODEL_MASTER b,EMGT.SALES_ENTRY c');
dm.SQLQuery7.SQL.Append('where c.empno=a.empno and c.brand=b.brand and c.code=b.code '); if not (combobox1.text = '') and not (combobox2.Text = '') then
dm.SQLQuery7.SQL.Append('and ( a.deptsect between ' #39 combobox1.Text #39 ' and ' #39 combobox2.Text #39 ')');
if not (combobox1.Text = '') and (combobox2.Text = '') then
dm.SQLQuery7.SQL.Append('and ( a.deptsect = ' #39 combobox1.Text #39 ')');
if not (combobox2.Text = '') and (combobox1.Text = '') then
dm.SQLQuery7.SQL.Append('and ( a.deptsect = ' #39 combobox2.Text #39 ')'); if not (combobox3.text = '') and not (combobox4.Text = '') then
dm.SQLQuery7.SQL.Append('and ( c.code between ' #39 combobox3.Text #39 ' and ' #39 combobox4.Text #39 ')');
if not (combobox3.Text = '') and (combobox4.Text = '') then
dm.SQLQuery7.SQL.Append('and ( c.code = ' #39 combobox3.Text #39 ')');
if not (combobox4.Text = '') and (combobox3.Text = '') then
dm.SQLQuery7.SQL.Append('and ( c.code = ' #39 combobox4.Text #39 ')');
|
NO.5
初階會員 發表:18 回覆:35 積分:25 註冊:2005-02-23 發送簡訊給我 |
|
liangzai
初階會員 發表:77 回覆:91 積分:33 註冊:2004-08-13 發送簡訊給我 |
|
NO.5
初階會員 發表:18 回覆:35 積分:25 註冊:2005-02-23 發送簡訊給我 |
1. 不設定PRIMARY KEY的TABLE,在Oracle照樣可以執行,只不過該TABLE中RECORD的"唯一性"是不被保證的,當你要查詢某一筆資料,卻出現兩筆以上的回傳值時,怎麼知道哪一筆才是正確的呢!!而且設為PRIMARY KEY的FIELD會自動成為預設的INDEX,在RECORD數十萬筆且沒有額外設定INDEX的情況下,會嚴重降低QUERY的速率. 2. Code利用[格式]\[#]按鈕貼上就能使用縮排,將您的Code稍微修改如下:
WITH dm.SQLQuery7.SQL DO BEGIN Text:='select a.*,b.model,unitprice,brand,code,c.voucherno,code,orderqty'; Append('from EMGT.HR_MASTER a,EMGT.MODEL_MASTER b,EMGT.SALES_ENTRY c'); Append('where c.empno = a.empno and c.brand = b.brand and c.code = b.code '); if (combobox1.text <> '') and (combobox2.Text <> '') then Append('and ( a.deptsect between ''' combobox1.Text ''' and ''' combobox2.Text ''')') else if (combobox1.Text <> '') and (combobox2.Text = '') then Append('and ( a.deptsect = ''' combobox1.Text ''')') else if (combobox1.Text = '') and (combobox2.Text <> '') then Append('and ( a.deptsect = ''' combobox2.Text ''')'); if (combobox3.text <> '') and (combobox4.Text <> '') then Append('and ( c.code between ''' combobox3.Text ''' and ''' combobox4.Text ''')') else if (combobox3.Text <> '') and (combobox4.Text = '') then Append('and ( c.code = ''' combobox3.Text ''')') else if (combobox3.Text = '') and (combobox4.Text <> '') then Append('and ( c.code = ''' combobox4.Text ''')'); END;在SQL COMMAND的敘述上在下認為沒有問題. |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |