topic Re: Two updates in one Oracle pass through in New SAS User

Two updates in one Oracle pass through

sameer112217 — Thu, 09 May 2019 12:35:26 GMT

I am trying to run an Oracle Passthrough for multiple updates in a table but I am getting an error. If I run only one update, the code runs fine. How can I insert multiple update statement in one passthrough. For example if I remove the second update statement, I dont get any error.

proc sql ;
connect to oracle as oracm ( PATH=** USER=**** PASSWORD="***" );
RESET NOPRINT;
EXECUTE (

update customer_sas_dq_output_New set RULE1 =1 where NVL(RULE1,null)<> 1
and policyno in (select policyno from Customer_Sas_DQ_Output_3yr_new where RULE1 =1);

update customer_sas_dq_output_New set RULE1 =0 where NVL(RULE1,null)<> 0
and policyno in (select policyno from Customer_Sas_DQ_Output_3yr_new where RULE1 =0);

) by oracm;
Disconnect from oracm;
Quit;

Re: Two updates in one Oracle pass through

sameer112217 — Thu, 09 May 2019 12:35:56 GMT

Here is the error

ERROR: ORACLE execute error: ORA-00933: SQL command not properly ended.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
40 Disconnect from oracm;
NOTE: Statement not executed due to NOEXEC option.
40 !
41 Quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.02 seconds

Re: Two updates in one Oracle pass through

Tom — Thu, 09 May 2019 12:58:54 GMT

I don't understand.

Just use two EXECUTE statements to execute your two Oracle statements.

You can do them both in the same PROC step.

EXECUTE (
update customer_sas_dq_output_New set RULE1 =1 
where NVL(RULE1,null)<> 1
  and policyno in (select policyno from Customer_Sas_DQ_Output_3yr_new where RULE1 =1)
) by oracm;
EXECUTE (
update customer_sas_dq_output_New set RULE1 =0 
where NVL(RULE1,null)<> 0
  and policyno in (select policyno from Customer_Sas_DQ_Output_3yr_new where RULE1 =0)
) by oracm;

Re: Two updates in one Oracle pass through

sameer112217 — Thu, 16 May 2019 08:29:27 GMT

I did the same