英文:
SQL query with seq and select
问题
SQL错误：
ORA-00936：缺少表达式
我需要从序列中插入ID，然后从SELECT查询中插入数据。
如何修复它？
英文:
INSERT INTO table (id, scnd_id, thrd_id, date_act)
VALUES(
SOME_SEQ.nextval,
(SELECT
t2.scnd_id,
t2.thrd_id,
MAX(t3.ppn_dt)
FROM table2
LEFT JOIN table3 t3
ON t2.thrd_id = t3.thrd_id
GROUP BY t2.thrd_id, t2.scnd_id));
SQL error:
> ORA-00936: missing expression
I need to insert id from seq and data from select query
How to fix it?
答案1
得分: 1
像这样：
insert into table (id, scnd_id, thrd_id, date_act)
select some_seq.nextval,
x.scnd_id,
x.thrd_id,
x.ppn_dt
from (select
t2.scnd_id,
t2.thrd_id,
max(t3.ppn_dt) ppn_dt
from table2 t2 left join table3 t3 on t2.thrd_id = t3.thrd_id
group by t2.thrd_id, t2.scnd_id
) x;
英文:
Like this:
insert into table (id, scnd_id, thrd_id, date_act)
select some_seq.nextval,
x.scnd_id,
x.thrd_id,
x.ppn_dt
from (select
t2.scnd_id,
t2.thrd_id,
max(t3.ppn_dt) ppn_dt
from table2 t2 left join table3 t3 on t2.thrd_id = t3.thrd_id
group by t2.thrd_id, t2.scnd_id
) x;
答案2
得分: 1
可以创建一个在插入表之前触发的触发器，当id为空时，使用sequence.nexval：
CREATE OR REPLACE TRIGGER "T_I_TABLE"
BEFORE INSERT ON TABLE
FOR EACH ROW
BEGIN
--
IF inserting THEN
IF :new.id IS NULL THEN
:new.id := SOME_SEQ.nextval;
END IF;
--
END IF;
--
END T_I_TABLE;
然后，您的插入操作可以如下所示：
INSERT INTO TABLE
(scnd_id, thrd_id, date_act)
(SELECT t2.scnd_id, t2.thrd_id, MAX(t3.ppn_dt)
FROM table2
LEFT JOIN table3 t3
ON t2.thrd_id = t3.thrd_id
GROUP BY t2.thrd_id, t2.scnd_id);
英文:
You can create a trigger before insert table and when id is null put the sequence.nexval
CREATE OR REPLACE TRIGGER "T_I_TABLE"
BEFORE INSERT ON TABLE
FOR EACH ROW
BEGIN
--
IF inserting THEN
IF :new.id IS NULL THEN
:new.id := SOME_SEQ.nextval;
END IF;
--
END IF;
--
END T_I_TABLE;
Then your insert can be this:
INSERT INTO TABLE
(scnd_id, thrd_id, date_act)
(SELECT t2.scnd_id, t2.thrd_id, MAX(t3.ppn_dt)
FROM table2
LEFT JOIN table3 t3
ON t2.thrd_id = t3.thrd_id
GROUP BY t2.thrd_id, t2.scnd_id);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论