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

Answers


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;

Need Your Help

Switching Sql Session State databases

c# session session-state multi-tenant sql-session-state

I have one instance of web application connected to multiple databases. Based on the domain name (for example www.shop1.com, www.shop2.com, ..) I switch the connection string in the Entity Framewor...

Spring Config file consisting of List of Implementations

java spring configuration dependency-injection

I Am very new to Spring. I have an Interface (MessageHandler ) which has a get method, this method returns a list of Implementations of another interface (messageChecker).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.