11-16-2017 09:56 PM
I am playing with pass-though option on SAS DI SQL transformations (SAS 9.4, SAS DI 4.9). My source and target objects are in different Oracle schemas and target schema can see source one's objects. I want to create a job to read data from source schema and put result into target schema. The problem is that as soon as I invoke pass-though automatically generated code uses source library to connect to database, but I need it to use target one. Is there any way I can do it without user-defined code?
I am creating a job in SAS DI using SQL SET transformation.
Source tables (Table 1 and Table 2) physical location is in Oracle library "Source_library" (using authdomain Source_Auth); Target_Table is located in Oracle library "Target_library" (using authdomain Target_Auth);
The generated code look like:
proc sql; connect to ORACLE ( PATH="<database>" AUTHDOMAIN=Source_Auth" ); execute ( insert into TARGET.TARGET_TABLE /* Returns all unique rows from the two query results. */ select distinct TABLE1.ID as ID from SOURCE.TABLE1 TABLE1 union select distinct TABLE2.ID as ID from SOURCE.TABLE2 TABLE2 ) by ORACLE;
I need SAS DI to look like
proc sql; connect to ORACLE ( PATH="<database>" AUTHDOMAIN=Target_Auth" );
11-21-2017 04:09 AM
I'm not sure if this would work, but try something like adding another input table as _INPUT1 which is from your target database, but don't use it in your query. I think DI will use _INPUT1 to determine the connection string.