sql查询-插入

发布于 2024-09-08 23:04:55 字数 456 浏览 6 评论 0原文

我有下面的sql脚本。当 select 查询没有找到记录时，insert 语句就不会插入任何记录。如果选择查询没有找到记录，我希望插入一条记录，其中包含新的序列号和其他具有空值的字段。我该怎么办呢。

insert into testing.test_ref_details(SEQNUM, TEST_TYPE,TEST_REF_NO)

select '&NEXT_SEQ_NO', '1',max(test_ref_no) as prev_test_ref1 
from    testing.test_runs_status
where   test_type = 1
and run_status = 1
and test_end_dt = (select last_day(add_months(trunc(sysdate),-6))+2 from dual)
group by test_end_dt

;

原文

I have the below sql script. When there is no record found by the select query there are no records inserted by the insert statement.
If no records found by select query i want to have a record inserted with the new sequence numbers and other fields with null values.
how can i do it.

insert into testing.test_ref_details(SEQNUM, TEST_TYPE,TEST_REF_NO)

select '&NEXT_SEQ_NO', '1',max(test_ref_no) as prev_test_ref1 
from    testing.test_runs_status
where   test_type = 1
and run_status = 1
and test_end_dt = (select last_day(add_months(trunc(sysdate),-6))+2 from dual)
group by test_end_dt

;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

决绝 2024-09-15 23:04:55

insert into testing.test_ref_details(SEQNUM, TEST_TYPE,TEST_REF_NO)
WITH q AS (
select '&NEXT_SEQ_NO' a, '1' b,max(test_ref_no) as prev_test_ref1 
from    testing.test_runs_status
where   test_type = 1
and run_status = 1
and test_end_dt = (select last_day(add_months(trunc(sysdate),-6))+2 from dual)
group by test_end_dt
)
SELECT a, b, prev_test_ref1 FROM q
UNION ALL
SELECT '&NEXT_SEQ_NO', NULL, NULL FROM DUAL
WHERE NOT EXISTS (SELECT NULL FROM q);

insert into testing.test_ref_details(SEQNUM, TEST_TYPE,TEST_REF_NO)
WITH q AS (
select '&NEXT_SEQ_NO' a, '1' b,max(test_ref_no) as prev_test_ref1 
from    testing.test_runs_status
where   test_type = 1
and run_status = 1
and test_end_dt = (select last_day(add_months(trunc(sysdate),-6))+2 from dual)
group by test_end_dt
)
SELECT a, b, prev_test_ref1 FROM q
UNION ALL
SELECT '&NEXT_SEQ_NO', NULL, NULL FROM DUAL
WHERE NOT EXISTS (SELECT NULL FROM q);

回复收藏 0 原文

吃颗糖壮壮胆 2024-09-15 23:04:55

PL/SQL 解决方案：

begin
  insert into testing.test_ref_details(SEQNUM, TEST_TYPE,TEST_REF_NO)
  select '&NEXT_SEQ_NO', '1',max(test_ref_no) as prev_test_ref1 
  from    testing.test_runs_status
  where   test_type = 1
  and run_status = 1
  and test_end_dt = (select last_day(add_months(trunc(sysdate),-6))+2 from dual)
  group by test_end_dt;

  if sql%rowcount = 0 then
    insert into testing.test_ref_details(SEQNUM)
    values ('&NEXT_SEQ_NO');
  end if;
end;

A PL/SQL solution:

begin
  insert into testing.test_ref_details(SEQNUM, TEST_TYPE,TEST_REF_NO)
  select '&NEXT_SEQ_NO', '1',max(test_ref_no) as prev_test_ref1 
  from    testing.test_runs_status
  where   test_type = 1
  and run_status = 1
  and test_end_dt = (select last_day(add_months(trunc(sysdate),-6))+2 from dual)
  group by test_end_dt;

  if sql%rowcount = 0 then
    insert into testing.test_ref_details(SEQNUM)
    values ('&NEXT_SEQ_NO');
  end if;
end;

回复收藏 0 原文

~没有更多了~