When i ran the below queries it's failing in the second query becuase prev_test_ref1 variable is not defined. If i remove the insert statement in the first query ,run again then it's working and using the prev_test_ref1 value from the first sql query in second query. Is it because of variable scope? How can i resolve this with the insert statement.
column prev_test_ref1 new_value prev_test_ref1 ; insert into testing.test_ref_details(TEST_TYPE,TEST_REF_NO) select '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 ;
column last_test_end_dt new_value last_test_end_dt; select to_char(test_completion_dt,'DD-MON-YYYY HH24:MI:SS') as last_test_end_dt from testing.test_runs_status where test_ref_no = '&prev_test_ref1';