06-27-2016 10:51 AM
I want to create stored process in SAS Enterpriseguide for the following oracle sql code, kindly tell me how to write stored process for it?
here there are two parameter :1)start year 2)end year
create or replace
START_YEAR IN NUMBER
, END_YAER IN NUMBER
SOURCE_SYSTEM VARCHAR(500) := '(2,3,4,14,15)';
POLICY_TYPE VARCHAR(500):= '(1,15)';
--STEP1: GET COVERAGE INFO
--DELETE IF TABLE EXIST
EXECUTE IMMEDIATE ('create table tcoverages_summary as
SELECT /*+ PARALLEL */
EXTRACT(YEAR FROM ACTUAL_DT) as YEAR_CT,
EXTRACT(MONTH FROM ACTUAL_DT) as MONTH_CT,
FROM TCOVERAGES_SUMMARY@MISP.AIG.COM T1
(T1.MONTH_SK = T2.DATE_SK)
-- TBROKER_DIMENSION@MISP.AIG.COM T3
T3.BROKER_SK = T1.bROKER_SK*/
WHERE POLICY_TYPE_SK IN '|| POLICY_TYPE ||' and
source_system_sk NOT IN '|| SOURCE_SYSTEM ||' AND
SNAPSHOT_TYPE_IN = 0 AND
EXTRACT(YEAR FROM ACTUAL_DT)>= '||START_YEAR||' AND
EXTRACT(YEAR FROM ACTUAL_DT)<= '||END_YAER||'');
06-29-2016 09:14 AM
The whole set up seems a bit awkward. Creating a SAS Stored Process that creates/calls a Stored Procedure, that does...what?
Elaborate about the use case so we can can give adequate directions.
And if we look from the other direction, what do you know about stored process, and not? What have you tried so far?
Perhaps start up with a simple "hello world" example, and take it from there...