Hi Gurus, I tried to run the below oracle query in SAS EG under proc SQL but I am getting the error as shown below. The same query runs fine in oracle database but here I am getting issues. Tried adjusting the brackets too but still no goo. Can any one help here LIBNAME CPS ORACLE USER = user PASSWORD = pass PATH='cpsprd_ro' SCHEMA = 'CPS_OWNER';
LIBNAME MIS ORACLE USER = user PASSWORD = pass PATH='cpsprd_ro' SCHEMA = 'MIS_OWNER';
proc sql;
connect to oracle (user=user password=pass path='cpsprd_ro' readbuff=100000);
create table want as select *
from connection to oracle
(
select
base.dt, base.usr_id, base.id,base.USER_NAME,base.name,
COALESCE(GROSS_HOURS,0) as GROSS_HOURS,
COALESCE(FLEX_ADJ,0)as FLEX_ADJ,
COALESCE(OVERTIME,0) as OVERTIME,
COALESCE(BORROWED,0) as BORROWED,
COALESCE(ONLOAN,0) as ONLOAN,
COALESCE(HOLYDAYS,0) as HOLIDAYS,
COALESCE(SICKNESS,0) as SICKNESS,
COALESCE(SPECIAL_JOB,0) as SPECIAL_JOB,
COALESCE(MGNT_ADJ,0) as MGNT_ADJ,
COALESCE(TRAINING,0) as TRAINING,
COALESCE(HOURS_ADJUSTMENT,0)as HOURS_ADJ,
COALESCE(COMPLETED_HOURS,0)as COMPLETED_HOURS,
COALESCE(COMPLETED_FOLDERS,0)as COMPLETED_FOLDERS,
base.BRL_ID
FROM(
SELECT dt, t2.UGP_ID,t2.USR_ID,t2.GRP_ID,t2.NAME,t2.BRL_ID, t2.ID, t2.user_name
FROM (
select TRUNC(SYSDATE,'y')-1+ROWNUM dt
from all_objects
where ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')
)dtz
CROSS JOIN
(
select UG.UGP_ID,UG.USR_ID,UG.GRP_ID,CG.NAME,CG.BRL_ID,USR.ID, USR.NAME as USER_NAME
from CPS.CPS_USER_GROUP UG
left join CPS.CPS_GROUP CG
on UG.GRP_ID = CG.GRP_ID
left join CPS.CPS_USER USR
on UG.USR_ID = USR.USR_ID
-- when you need to add the new team, you need to add here
-- updated 24/05 to include settlement team
where CG.BRL_ID in (1,16) and CG.NAME in ('DOCUMENTATION','DOC MANAGEMENT','DISBURSALS TEAM',
'SUPPORT SERVICES TEAM','VERIFICATIONS','HOUSING LOANS VALIDATIONS','COMPLEX - SECURITIES','DOC PREP','SETTLEMENTS',
'NATIONAL EXAMINATIONS','DISBURSALS','FBI','LEG-ENDS','PHONE TEAM','RECORDS MAN','THE UNTOUCHABLES',
'MAVERICKS','DREAM TEAM','NINJA TURTLES','TEAM ELITE','TOP GUNS','TRANSFORMERS','CMS NINJA TURTLES','DPS DISCHARGE','DPS EXAMINATIONS')or CG.NAME like '%HLT%'
or CG.NAME like '%LEVEL 23%'
)t2
)base
LEFT JOIN MIS.MIS_USER_SUMMARY t1
on t1.USR_ID = base.USR_ID and t1.action_date = base.dt and t1.brl_id = base.brl_id
LEFT JOIN
(
select adj.dt, adj.usr_id,
sum(adj.flex_adj)as FLEX_ADJ,
sum(adj.overtime)as OVERTIME,
sum(adj.BORROWED) as BORROWED,
sum(adj.ONLOAN) as ONLOAN,
sum(adj.HOLIDAYS) as HOLYDAYS,
sum(adj.SICKNESS) as SICKNESS,
sum(adj.SPECIAL_JOB) as SPECIAL_JOB,
sum(adj.MGNT_ADJ) as MGNT_ADJ,
sum(adj.TRAINING) as TRAINING
FROM(
select
t4.DAY as dt,
t4.USR_ID,
case when hat_id=1 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as FLEX_ADJ,
case when hat_id=2 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as OVERTIME,
case when hat_id=3 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as BORROWED,
case when hat_id=7 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as ONLOAN,
case when hat_id=4 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as HOLIDAYS,
case when hat_id=6 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as SICKNESS,
case when hat_id=8 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as SPECIAL_JOB,
case when hat_id=9 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as MGNT_ADJ,
case when hat_id=5 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as TRAINING
from MIS.MIS_USER_WORK_ADJUSTMENTS t4
where t4.DAY>='25-JUL-2016'
) adj
GROUP BY adj.dt, adj.usr_id
)adj1
on adj1.USR_ID = base.USR_ID and adj1.dt = base.dt
where base.dt >='19-JUN-2017' and base.dt<sysdate+21 -- filter to update the date range
order by base.usr_id
);
disconnect from oracle;
quit; -------------------ERROR---------------------------------- _ 22 200 ERROR 22-322: Syntax error, expecting one of the following: a name, ;, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE. ERROR 200-322: The symbol is not recognized and will be ignored.
... View more