一直出現"SQL的結束命令有問題" |
|
尚未結案
|
|
narcysion
一般會員   發表：54 回覆：18 積分：14 註冊：2003-03-14 發送簡訊給我 |
我使用的資料庫是ORACLE 8i
我在TQuery裡寫以下的SQL程式，
但一直出現"SQL的結束命令有問題"，
請問哪裡寫錯了 update RunCard_list
set RunCard_list.rnl_Mac_ID = A.Mac_ID,
RunCard_list.rnl_Clk_No = A.Clk_No,
RunCard_list.rnl_B_Date = A.TxBDateTime,
RunCard_list.rnl_E_Date = A.TxEDateTime,
RunCard_list.rnl_ClassType = A.ClassType,
RunCard_list.rnl_IpqcQty = A.IpqcQty,
RunCard_list.rnl_PassQty = A.PassQty,
RunCard_list.rnl_NoPassQty = A.NoPassQty,
RunCard_list.rnl_CaseID = A.CaseID,
RunCard_list.rnl_Sign = A.Sign,
RunCard_list.rnl_Reason = A.Reason,
RunCard_list.rnl_Remark = A.Remark
from OPS01 A
where A.OPS_Lot_No = RunCard_list.rnl_rnc_LotNo
and A.OPS_P_Sec = RunCard_list.rnl_P_Sec
and A.OPS_Seq_No = RunCard_list.rnl_Seq_No
and A.OPS_InputFC =:v_InputFC
|
|
deity
尊榮會員  發表：90 回覆：876 積分：678 註冊：2003-05-09 發送簡訊給我 |
narcysion你好：
update RunCard_list
set RunCard_list.rnl_Mac_ID = A.Mac_ID,
RunCard_list.rnl_Clk_No = A.Clk_No,
RunCard_list.rnl_B_Date = A.TxBDateTime,
RunCard_list.rnl_E_Date = A.TxEDateTime,
RunCard_list.rnl_ClassType = A.ClassType,
RunCard_list.rnl_IpqcQty = A.IpqcQty,
RunCard_list.rnl_PassQty = A.PassQty,
RunCard_list.rnl_NoPassQty = A.NoPassQty,
RunCard_list.rnl_CaseID = A.CaseID,
RunCard_list.rnl_Sign = A.Sign,
RunCard_list.rnl_Reason = A.Reason,
RunCard_list.rnl_Remark = A.Remark
from OPS01 A去除这行，试试
where A.OPS_Lot_No = RunCard_list.rnl_rnc_LotNo
and A.OPS_P_Sec = RunCard_list.rnl_P_Sec
and A.OPS_Seq_No = RunCard_list.rnl_Seq_No
and A.OPS_InputFC =:v_InputFC
看看update帮助：
UPDATE table_reference
SET column_ref = update_atom [, column_ref = update_atom...]
[WHERE predicates] 發表人 - deity 於 2004/04/08 17:05:36
|
|
deity
尊榮會員 發表：90 回覆：876 積分：678 註冊：2003-05-09 發送簡訊給我 |
narcysion你好：
update RunCard_list
set RunCard_list.rnl_Mac_ID = A.Mac_ID,
RunCard_list.rnl_Clk_No = A.Clk_No,
RunCard_list.rnl_B_Date = A.TxBDateTime,
RunCard_list.rnl_E_Date = A.TxEDateTime,
RunCard_list.rnl_ClassType = A.ClassType,
RunCard_list.rnl_IpqcQty = A.IpqcQty,
RunCard_list.rnl_PassQty = A.PassQty,
RunCard_list.rnl_NoPassQty = A.NoPassQty,
RunCard_list.rnl_CaseID = A.CaseID,
RunCard_list.rnl_Sign = A.Sign,
RunCard_list.rnl_Reason = A.Reason,
RunCard_list.rnl_Remark = A.Remark
from OPS01 A[/red]去除这行，试试
where A.OPS_Lot_No = RunCard_list.rnl_rnc_LotNo
and A.OPS_P_Sec = RunCard_list.rnl_P_Sec
and A.OPS_Seq_No = RunCard_list.rnl_Seq_No
and A.OPS_InputFC =:v_InputFC
看看update帮助：
UPDATE table_reference
SET column_ref = update_atom [, column_ref = update_atom...]
[WHERE predicates]
sorry,点多一份,烦版主或站务人员删除[/red]
——行径窄处，留一步与人行—— 發表人 - deity 於 2004/04/08 17:23:57
|
|
Fishman
尊榮會員  發表：120 回覆：1949 積分：2163 註冊：2006-10-28 發送簡訊給我 |
Hi narcysion, Oracle 中，似乎不接受此種寫法，你可以試試
UPDATE RUNCARD_LIST
SET RUNCARD_LIST.RNL_MAC_ID = (SELECT A.MAC_ID
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_CLK_NO = (SELECT A.CLK_NO
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_B_DATE = (SELECT A.TXBDATETIME
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_E_DATE = (SELECT A.TXEDATETIME
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_CLASSTYPE = (SELECT A.CLASSTYPE
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_IPQCQTY = (SELECT A.IPQCQTY
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_PASSQTY = (SELECT A.PASSQTY
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_NOPASSQTY = (SELECT A.NOPASSQTY
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_CASEID = (SELECT A.CASEID
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_SIGN = (SELECT A.SIGN
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_REASON = (SELECT A.REASON
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_REMARK = (SELECT A.REMARK
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC =:V_INPUTFC)
或是用 PL/SQL
DECLARE
OPS01_REC OPS01%ROWTYPE;
BEGIN
BEGIN
SELECT A.MAC_ID,
A.CLK_NO,
A.TXBDATETIME,
A.TXEDATETIME,
A.CLASSTYPE,
A.CLASSTYPE,
A.PASSQTY,
A.NOPASSQTY,
A.CASEID,
A.SIGN,
A.REASON,
A.REMARK
INTO OPS01_REC.MAC_ID,
OPS01_REC.CLK_NO,
OPS01_REC.TXBDATETIME,
OPS01_REC.TXEDATETIME,
OPS01_REC.CLASSTYPE,
OPS01_REC.CLASSTYPE,
OPS01_REC.PASSQTY,
OPS01_REC.NOPASSQTY,
OPS01_REC.CASEID,
OPS01_REC.SIGN,
OPS01_REC.REASON,
OPS01_REC.REMARK
FROM OPS01 A
WHERE A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
AND A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
AND A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
AND A.OPS_INPUTFC = :V_INPUTFC;
UPDATE RUNCARD_LIST
SET RUNCARD_LIST.RNL_MAC_ID = OPS01_REC.MAC_ID,
RUNCARD_LIST.RNL_CLK_NO = OPS01_REC.CLK_NO,
RUNCARD_LIST.RNL_B_DATE = OPS01_REC.TXBDATETIME,
RUNCARD_LIST.RNL_E_DATE = OPS01_REC.TXEDATETIME,
RUNCARD_LIST.RNL_CLASSTYPE = OPS01_REC.CLASSTYPE,
RUNCARD_LIST.RNL_IPQCQTY = OPS01_REC.CLASSTYPE,
RUNCARD_LIST.RNL_PASSQTY = OPS01_REC.PASSQTY,
RUNCARD_LIST.RNL_NOPASSQTY = OPS01_REC.NOPASSQTY,
RUNCARD_LIST.RNL_CASEID = OPS01_REC.CASEID,
RUNCARD_LIST.RNL_SIGN = OPS01_REC.SIGN,
RUNCARD_LIST.RNL_REASON = OPS01_REC.REASON,
RUNCARD_LIST.RNL_REMARK = OPS01_REC.REMARK;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
------
Fishman |
發表時間：2004-04-08 16:20:06
IP:61.222.xxx.xxx 未訂閱
系統時間：2025-01-31 7:20:55
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |