Inserting records into Oracle database using SQL passthrough query doesn’t shows the number of records inserted to the table.
Am using SQL passthrough query for inserting records to the DB table in Prod SQL.
The macro code is being included in the parent code that is scheduled for processing. When I run manually the macro code in SAS EG, I can see the number of records inserted in the table.
But when running in the include code in the parent code together, it doesn’t shows any logs messages on how many records are inserted in the oracle table.
In SAS EG – can see –
ORACLE: 0 rows inserted/updated/deleted. 153 1556630270 no_name 0 SQL (2)
While running in the parent code – cannot see the records inserted in the oracle table as in below log. Already using %PUT &SQLXRC. &SQLXMSG. ; and this only shows 0 as return code is zero with no error message –
MPRINT(EXECUTESEQUERY): *SE scrub stg;
MPRINT(EXECUTESEQUERY): proc sql;
MPRINT(EXECUTESEQUERY): CONNECT TO oracle ( path=xxx authdomain="xx") ;
MPRINT(EXECUTESEQUERY): execute ( INSERT INTO tablename ( col1, col2 ) select
- app_transaction_key, T. scrub_key, sum(T.flag) as override_type from ( select a.app_transaction_key, b.scrub_key, 1 as flag from
schema.v_transaction_current a, schema.master b where trunc(a.s_effective_from) > '30-APR-2019' and
b.query_group_key = 80 and WAREHOUSE_CODE='PPP' AND CUSTOMER_NAME='SAN' union Select a.app_transaction_key, a.scrub_key, -1 as
flag from schema.v_transaction_current c, schema.v_tran_current a, schema.master b where
c.app_transaction_key=a.app_transaction_key and b.scrub_key = a.scrub_key and trunc(c.s_effective_from) > '30-APR-2019' and
b.query_group_key = 80 ) t group by T.app_transaction_key, T.scrub_key ) by oracle ;
0
MPRINT(EXECUTESEQUERY): Disconnect from oracle;
MPRINT(EXECUTESEQUERY): quit;
NOTE: PROCEDURE SQL used (Total process time):