ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

ivyc — Wed, 10 Jul 2019 17:08:27 GMT

HI Trying to run the code below but keep on getting an error

Can you help me figure out why ?

See code below:

PROC SQL;
CONNECT TO ORACLE (PATH = COLLP USER = &USERID. PASSWORD = &PWD.);
CREATE TABLE mytable AS
SELECT * FROM CONNECTION TO ORACLE (

SELECT
lf.list_name,
lf.priority,
wk.call_id,
wk.parent_call_id,
dl.mds_rec_id,
dl.acct_contact_key,
dl.system_no,
dl.principle_num,
dl.file_date,
dl.DWNLD_DT,
CASE WHEN ex.mds_field_value IS NULL THEN 'N' ELSE 'Y' END as Excluded_Account_Flag,
CASE WHEN exZip.mds_field_value IS NULL THEN 'N' ELSE 'Y' END as Excluded_Zip_Flag,
CASE WHEN dl.days_delq <= '029' THEN '1'
WHEN dl.days_delq <= '059' THEN '2'
WHEN dl.days_delq <= '089' THEN '3'
WHEN dl.days_delq <= '119' THEN '4'
WHEN dl.days_delq <= '149' THEN '5'
WHEN dl.days_delq <= '179' THEN '6'
WHEN dl.days_delq <= '209' THEN '7'
ELSE '0' END as Due_Stage,
wk.sso_id,
dl.min_tz ,
dl.max_tz,
wk.mds_status,
wk.mds_term_cd,
acl.ACTION_DESC as ACTION_DESC,
SUM(acl.ATTEMPT_CNT) as ATTEMPT_CNT,
SUM(acl.CALL_CNT) as CALL_CNT,
SUM(acl.CONNECT_CNT) as CONNECT_CNT,
SUM(acl.CONTACT_CNT) as CONTACT_CNT,
SUM(acl.CUST_CALL_CNT) as CUST_CALL_CNT,
SUM(acl.FIND_CNT) as FIND_CNT,
SUM(acl.NO_PROMISE_CNT) as NO_PROMISE_CNT,
SUM(acl.PROMISE_CNT) as PROMISE_CNT,
SUM(acl.RPC_CNT) as RPC_CNT,
COUNT(DISTINCT CASE WHEN
dl.HOME_PH = '0000000000'
AND dl.MOBILE_NUMBER = '0000000000'
AND dl.WORK_PH = '0000000000'
AND dl.FAX_PH = '0000000000'
AND dl.PAGER_PH = '0000000000'
THEN dl.mds_rec_id ELSE NULL END) as No_Phone_Accounts,
COUNT(DISTINCT CASE WHEN wk.mds_term_cd = 'ML' THEN wk.mds_rec_id ELSE NULL END) as Just_Looking,
SUM(dl.mds_acct_status) as mds_acct_status_attempts

FROM mpoc.dbo.MDS_LOAD_FACT dl
LEFT JOIN MDS_EXCLUSION_FACT ex on dl.acct_contact_key = ex.mds_field_value
and ex.header_date = dl.header_date
LEFT JOIN MDS_EXCLUSION_FACT exZip on dl.zip = exZip.mds_field_value
and exZip.header_date = dl.header_date
LEFT JOIN MDS_ACTV_WKST_FACT wk on dl.acct_contact_key = wk.acct_contact_key
and dl.header_date = wk.header_date
LEFT JOIN dbo.action_cd_lkup acl on wk.mds_term_cd = acl.action_cd
LEFT JOIN MDS_LISTS_PRIORITY_FACT lf on lf.acct_contact_key = dl.acct_contact_key
and lf.header_date = dl.header_date

where dl.file_dt >='01Jan2019'd and dl.file_dt<='28Feb2019'd);
QUIT;

Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

Reeza — Wed, 10 Jul 2019 17:13:01 GMT

When you use pass through sql you need to use Oracle SQL, not SAS SQL. From a quick read of the code, some issues pop up such as date literals, I think you'll need to specify those in a format that Oracle understands. I usually use to_date("2019-01-01", "YYYY-MM-DD") to specify my dates.

Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

ivyc — Wed, 10 Jul 2019 17:18:22 GMT

I changed the date and still getting the error .What else would be ?Thank you

Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

Reeza — Wed, 10 Jul 2019 17:26:49 GMT

Nothing jumps out at me as being invalid, but I'm not that familiar with Oracle SQL. I would test it by reducing the query to the basics and start adding back components one by one to see what's causing the issue.

Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

Reeza — Wed, 10 Jul 2019 17:27:13 GMT

Or test it on a SQL server first if you have access, the errors there should be more descriptive.

topic ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended. in SAS Programming

ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.

Re: ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.