sql query - insert

Question

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

;

Answer 1

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

Answer 2

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;