天道酬勤,学无止境

ora-00928

Oracle CTE Merge

I am trying a simple merge statement using a CTE(Common table expression) . But it gives an error MERGE INTO emp targ USING ( * ERROR at line 4: ORA-00928: missing SELECT keyword Is the CTE not allowed in a merge statement? My Sql is below: WITH cte AS ( SELECT empno, ename FROM EMP) MERGE INTO emp targ USING (SELECT * FROM cte) src ON (targ.empno = src.empno) WHEN MATCHED THEN update SET targ.ename = src.ename WHEN NOT MATCHED THEN insert (empno,ename) VALUES (src.empno,src.ename) /

2021-07-28 18:28:35    分类:问答    sql   oracle   merge   ora-00928

java.sql.SQLException: ORA-00928: missing SELECT keyword. when inserting record to DB using JDBC

I get an error when I try to insert some rows to a db. so here is the code try { String insertStmt = "INSERT into " + "MY_TABLE('RECORD_TYPE', 'FILE_TYPE', 'DATE', 'BATCH_NO', 'RECORD_COUNT')" + "VALUES(?, ?, ?, ?, ?);"; PreparedStatement pstmt = super.con.prepareStatement(insertStmt); pstmt.setString(1, input[0]); pstmt.setString(2, input[1]); pstmt.setString(3, input[2]); pstmt.setString(4, input[3]); pstmt.setString(5, input[4]); System.out.println("Insert rows : " + pstmt.executeUpdate()); } catch (SQLException sqle) { System.out.println(sqle.getMessage()); sqle.printStackTrace(); } catch

2021-05-01 14:18:53    分类:问答    java   oracle   jdbc   ora-00928

Oracle — WITH CLAUSE => MERGE? (Syntax error, )

I'm trying to get the WITH clause to work with merge in Oracle, but for some reason I can't get it working. I'm sure it is something obvious, but I just haven't seen it. -- behold, the wonders of fake data WITH X AS ( SELECT 'moo' AS COW, 'woof' AS CAT, (SELECT MAX( DECIBELS ) FROM ANIMALIA WHERE COW = 'moo' ) AS DECIBELS FROM DUAL ) MERGE INTO ANIMALIA D USING X WHEN MATCHED THEN UPDATE SET D.COW = X.COW; EDIT I actually found out how to manage this (before I submitted the question), but I think that since it took me quite some time to find the answer, hopefully leaving this question up will

2021-05-01 04:45:51    分类:问答    oracle   merge   with-statement   ora-00928   subquery-factoring

Oracle DELETE statement with subquery factoring

Trying to do this (works in SQL Server): WITH X AS (), Y AS (), Z AS () DELETE FROM TBL WHERE TBL.ID IN (SELECT ID FROM Z); This works in Oracle: WITH X AS (), Y AS (), Z AS () SELECT * FROM TBL WHERE TBL.ID IN (SELECT ID FROM Z); But the DELETE does not: ORA-00928: missing SELECT keyword My subqueries are rather large, is there a different syntax to get this to work?

2021-04-27 19:30:15    分类:问答    oracle   oracle10g   ora-00928   subquery-factoring