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;
精彩评论