开发者

sql query - insert

I have the below sql script. When there is no record found by the select query there are no re开发者_C百科cords 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

;


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);


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜