Nisar Nisar - 3 months ago 23
SQL Question

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);


showing an error
ORA-00928: missing SELECT keyword

Answer

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;