I'm running the following script to pull some results to a text table. When I run the script, it returns the script to the text file and no query results. Any idea as to why?
set feedback off
set heading off
set echo off
set define off
set linesize 500
spool \\1.1.1.1\w$\Customer_Service\Outgoing\Missing_PO_NN.txt
select p.po
from sv_order_check_nn p
where not exists (
select 1
from ordusctes o
where o.usctes_po = p.po);
/
SPOOL OFF
When I run the script, it returns the script to the text file and no query results. Any idea as to why?
Depends whether your query actually returns any rows. Did you first execute your query and check if it actually returns any rows?
select p.po from sv_order_check_nn p where not exists ( select 1 from ordusctes o where o.usctes_po = p.po); /
You are executing the query twice. You are using slash / as terminator in the end, which is going to execute whatever is in the buffer.
See this answer for similar problem and fix.
In your case, your query will be executed twice.
WHERE NOT EXISTS, its usually terrible for performance. Try doing aLEFT JOINinstead where the right table's join key is null.