Correlated Query Options

Use the _QRW / NO_QRW option to configure the server to enable or disable the query rewrite facility diagnostic output, which includes debugging and tracing information. The debugging and tracing output is generated when the server query rewrite facility detects subexpressions. The query rewrite facility then rewrites and executes the SQL code. The SQL code produces the intermediate results and the final rewritten SQL statement. By default, the server _QRW option for diagnostic output is not enabled.

/* Enable query rewrite diagnostics */
execute(reset _qrw)
 by sasspds ;

/* A second way to enable    */
/* query rewrite diagnostics */
execute(reset _qrw=1)
  by sasspds ;

/* A third way to enable     */
/* query rewrite diagnostics */
execute(reset no_qrw=0)
 by sasspds ;

/* Disable query rewrite diagnostics */
execute(reset no_qrw)
  by sasspds ;

/* A second way to disable query */
/* rewrite diagnostics           */
execute(reset _qrw=0)
 by sasspds ;

/* Another way to disable query */
/* rewrite diagnostics          */
execute(reset no_qrw=1)
  by sasspds ;

Use the _QRWENABLE / NO_QRWENABLE option to completely disable the server query rewrite facility. Disabling the query rewrite facility prevents the rewrite planner from intervening in the SQL flow and from making any optimizing rewrites. Typically, you do not specify this option unless you want to test whether an SQL statement runs faster without rewrite optimization, or if you suspect that the resulting rowset that you get from a query rewrite evaluation is incorrect.

/* Disable query rewrite */
/* facility              */
execute(reset no_qrwenable)
 by sasspds ;

/* A second way to disable */
/* query rewrite facility  */
execute(reset _qrwenable=0)
  by sasspds ;

/* Enable query rewrite  */
/* facility              */
execute(reset _qrwenable)
  by sasspds ;

/* A second way to enable  */
/* query rewrite facility  */
execute(reset _qrwenable=1)
  by sasspds ;

%let spdshost=localhost;
%let spdsport=5400;
%let user=anonymous;

libname spdslib sasspds 'tmp' 
  host="&spdshost" 
  serv="&spdsport" 
  user="&user" 

LIBGEN=YES IP=YES;

data spdslib.a; 
       do i=1 to 10; 
          x=i; 
          output; 
       end; 
run; 

 data spdslib.b; 
  do i=1 to 100; 
    x=i; 
      y=1+floor(100*ranuni(9999)); 
   output; 
  end; 
run; 

%let spdssqlr=_qrw;

proc sql _method; 
 
   select * from spdslib.a 
    where x in (select x from (select b.x from spdslib.b where b.y gt 50)) 
; 

quit;