從 CTE 插入

Question

<p><pre class="brush:php;toolbar:false;">使用 DTL AS （選擇 CMPI_代碼， CMN_CDTY_MTRL， CMI_WT_FACTOR， CMI_CNTRCT_RATE， 'PL', 當前_價格， TRM_程式碼， ROUND(((NVL(PRESENT_PRICE,1)*CMI_WT_FACTOR) / CMI_CNTRCT_RATE),2) AS PL_FACTOR 來自 VW_CMD_MATERIAL，其中 TRM_CODE = 41） INSERT iNTO IPA_PRCADJ_HDR(TRM_CODE,IPAPH_ADJ_FACTOR,IPAPH_AMT_CUR,IPAPH_REMARKS) 選擇 TRM_CODE,SUM(PL_FACTOR) AS PL_FACTOR,((SUM(PL_FACTOR)*10)) AS AMT_CUR,'asdf' FROM DTL GROUP BY (TRM_CODE);; <p>顯示錯誤 ORA-00928: 缺少 SELECT 關鍵字</p>

Answer 1

似乎 Insert 不能用作公用表表達式之後的第一個語句。試試這個，

INSERT iNTO IPA_PRCADJ_HDR(TRM_CODE,IPAPH_ADJ_FACTOR,IPAPH_AMT_CUR,IPAPH_REMARKS)
    SELECT * FROM 
    (

      WITH DTL AS
      (SELECT CMPI_CODE,
             CMN_CDTY_MTRL,
             CMI_WT_FACTOR,
             CMI_CNTRCT_RATE,
             'PL',
             PRESENT_PRICE,
             TRM_CODE,
             ROUND(((NVL(PRESENT_PRICE,1)*CMI_WT_FACTOR) / CMI_CNTRCT_RATE),2) AS PL_FACTOR
      FROM VW_CMD_MATERIAL WHERE TRM_CODE = 41
      )
      SELECT TRM_CODE,SUM(PL_FACTOR) AS PL_FACTOR,((SUM(PL_FACTOR)*10)) AS AMT_CUR,'asdf' 
      FROM DTL GROUP BY (TRM_CODE)
    );

Answer 2

這是從 CTE 插入表的語法：

-- CREATE TABLE tmp ( tmp_id NUMBER(10) );

INSERT INTO tmp( tmp_id )
  WITH cte AS (
    SELECT 1 AS tmp_id FROM dual
  )
  SELECT tmp_id
  FROM cte;