Convert PLSql components into a SAS program

[Banu]

Hi,
I have two quires in Oracle. 1st one is a main query. It contains joins on multiple tables and in this one of the table column value is passing into the Oracle function and it returns value to the main query.

Please suggest me how can write passing value into a Oracle function and returning values into a main query in SAS. Thanks InAdvance

[LinusH]

Please shaee an example on how you this today, as simplified as possible.
Also, would you like to populate the paramter?

[Banu]

I have join as below.

QUERY:

select a.country_name,

          b.state_name,

          c.city_name,

          d.product_type,

          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

           b.state_id=c.state_id and

           c.product_type_id=d.product_type_id;

In the above quey tables are taken from different users.

FUNCTION:

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 a.categ_id=b.categ_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. 

[SASKiwi]

This post has a clear explanation of how to run unmodified Oracle SQL in SAS:

https://communities.sas.com/t5/SAS-Enterprise-Guide/Urgent-help-in-converstion-of-oracle-to-AS/m-p/3...

[Reeza]

Search SQL Pass Thru

[LinusH]

As @Reeza says, you need to use explicit SQL pass thru from SAS. ASFAIK SAS implicit pass thru cannot call UDF's.
If you wish to SAS SQL skip calling the function. To me it looks like it could be solved by using the products table in a join instead. Probably my ignorance of PLSQL, but where does b.categ_id and price_id come from?

[Banu]

Hi Linush,

if I have only one PL/SQL procedure or function then I can call as mentioned below.

proc sql;

connect to oracle (user="oracleUserName" password="pass" path="serverPath" CONNECTION=GLOBAL CONNECTION_GROUP = SASAML);
       execute( execute MyPackage.MyProcedure(&param1, &param2) ) by oracle;
disconnect from oracle;

quit;

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. 

[Tom]

Don't use EXECUTE just use SELECT ... FROM CONNECTION TO ... 

select * from connection to oracle
(/* Place ORACLE code inside the () */
select ......
);

[Banu]

Hi Linush, thanks for your reply. If input tables exist in different databases and those tables are involved in joining condition.inthese case how can I use explicit SQL pass through.Thanks.

[Reeza]

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. 

[Banu]

Thanks Reza, Just want to confirm you one thing, Can I include Oracle function query in place of call_func(d.product_id) as product_name. I hope in Oracle we can perform select clause in place of column name in Select statement. Please advice.

[Reeza]

If it works on the server it will work in pass through. Either way, test it out, very easy to test. 

[Patrick]

@Banu

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> );

[LinusH]

You got the answer from @Tom.
The from connection to construct is also considered as explicit SQL pass through, and is used when you want to return something to the SAS session.