Menu
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
768
You can also use CTE to insert data into the SQL table. The CTE query definition includes the required data that you can fetch from existing tables using joins. Later, query CTE for inserting data into the target table.
Updatable CTEIf your CTE is based on a single table then you can update using CTE, which in turn updates the underlying table.
CTE can be used for both selects and DML (Insert, Update, and Delete) statements.
You can use a common table expression (CTE) to simplify creating a view or table, selecting data, or inserting data. Use a CTE to create a table based on another table that you select using the CREATE TABLE AS SELECT (CTAS) clause.
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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
