从CTE插入 [英] Insert into from CTE

问题描述

  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)
  INSERT iNTO IPA_PRCADJ_HDR(TRM_CODE,IPAPH_ADJ_FACTOR,IPAPH_AMT_CUR,IPAPH_REMARKS)
  SELECT TRM_CODE,SUM(PL_FACTOR) AS PL_FACTOR,((SUM(PL_FACTOR)*10)) AS AMT_CUR,'asdf' 
  FROM DTL GROUP BY (TRM_CODE);

显示错误ORA-00928: missing SELECT keyword

推荐答案

这是从CTE插入表中的语法:

This is the syntax to insert into a table from a 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;

这篇关于从CTE插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！