05-27-2017 06:27 AM
05-27-2017 02:09 PM
I have join as below.
call_func(d.product_id) as product_name
from country a,state b,city c,product_info d
where a.country_id=b.country_id and
In the above quey tables are taken from different users.
CREATE or REPLACE FUNCTION call_func(product_id IN NUMBER) RETURN VARCHAR2 IS acc_bal NUMBER(11,2); BEGIN SELECT a.product_name as produc_name INTO product_name FROM products a,categ b,price c WHERE a.product_id = product_id
and b.price_id=c.price_id; RETURN(product_name); END;
Please ignore in case of any syntax errors but query pupose is same.
Please let me know how can I do in SAS.
As of now, we copied required database tables in SAS and then doing SAS joins in proc sql.
So please suggest me in which ways how can i achieve above one.
Thanks In Advance.
05-27-2017 08:08 PM
This post has a clear explanation of how to run unmodified Oracle SQL in SAS:
05-27-2017 05:34 PM
05-29-2017 07:05 AM
if I have only one PL/SQL procedure or function then I can call as mentioned below.
connect to oracle (user="oracleUserName" password="pass" path="serverPath" CONNECTION=GLOBAL CONNECTION_GROUP = SASAML);
execute( execute MyPackage.MyProcedure(¶m1, ¶m2) ) by oracle;
disconnect from oracle;
If you see my query, Scenario is different.
For example, I have 10 variables in select clause and one of the variable value is passing to Oracle function and then doing
some business logic overe there and function returns back value into main query and rest of 9 columns are populated directly from other 9 tables by joining the tables.
Ex: select a,b,c,call_func(d) e,f,g from s1,s2......etc..
Please suggest me how can i achieve this in sas.
05-27-2017 09:55 PM
05-27-2017 10:06 PM
The same way you did before. It happens on the server, so if the server could run the code before it still can now.
If your data structure has changed that's a different story.
05-27-2017 10:33 PM
05-29-2017 07:07 PM
With pass-through SQL you can run anything that works directly in Oracle (i.e. using SQL Developer) out of SAS.
SAS sends the code in the pass-through block "as-is" to Oracle for exection. So if your code works out of SQL Developer then it will work out of a pass-through block in SAS.
If you want SAS to execute something in Oracle without returning a result then wrap the Oracle code into an EXECUTE() block, else if you want a result set back then use SELECT * from connection to <oracle> ( <your Oracle SQL> );
05-29-2017 12:37 PM