05-30-2013 04:58 PM
I have the following code which runs fine without the where statement. But as soon as I include it, I get an error:
Basedate = %sysfunc(Today(),date9.); /*todays
PrevMonthStartdate = %sysfunc(intnx(Month,
"&Basedate"d,-1,b), date9.); /*start date of the
PrevMonthEnddate = %sysfunc(intnx(Month,
"&Basedate"d,-1,e), date9.); /*end date of the
connect to odbc (user="&mida_username."
execute( Create table
select TRXN_DATE as TRANMONTH,
case when CHANNELTYPE = '@SES'
when CHANNELTYPE in ('@CALLCENTER','@IVR')
when CHANNELTYPE is null and TRANSACTIONTYPE in ('OTP','ENROLLMENT')
then 'Digi NM'
else 'Other' end as CHANNEL,TRANSACTIONTYPE as MODEL,
case when (TRXTYPECD = 'InternalTransfer' or TRXTYPECD
like '%Me2Me%') then 1
else 0 end as ME2ME,
ase when ISFILTERED = 'TRUE' then 1
else 0 end as ISFILTERED, TRXN_ID,CUSTID,AMOUNT
(select * from XXX.TL_X
distinct(trunc(TRXN_DATE,'MONTH')) BETWEEN TRUNC(ADD_MONTHS(SYSDATE, -1),'Month')
AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1)*/ where datepart(TRXN_DATE) = '&prevmonthstartdate'd
)a ) by odbc;
CLI execute error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis
Above is the error I get. I'm guessing I'm missing something obvious?
Thanks for your help guys.
05-30-2013 08:18 PM
Don't forget that everything in the Execute clause must be compatible with Oracle version of SQL. I don't know Oracle SQL but your WHERE clause looks very much like SAS SQL.
05-30-2013 08:59 PM
I, too, am not familiar with Oracle SQL, but:
I doubt if the following is valid. Shouldn't it be starting with "case"?
ase when ISFILTERED = 'TRUE' then 1
05-30-2013 09:56 PM
Art, those lost characters are only the consequence of a copy and paste bug that shows up when pasting directly from SAS enhanced editor to MS Internet Explorer. I suspect the problem is either with the datepart function or the date litteral syntax in the WHERE clause.
05-31-2013 03:27 AM
So i've moved the datepart to the first datastep which resolves one potential issue, with regards to the date macro (the date must match to the first date of the previous month), does anyone know how to do this in oracle/sql?
05-31-2013 04:11 AM
If you want to get help on Oracle SQL syntax, you are in the wrong forum.
Why don't you try to use implicit SQL pass-thru, many SAS functions will be translated by the SAS/ACCESS engine.
08-15-2014 10:51 AM
I have not found a solution (other than moving the date selection criteria from ORACLE sql to SAS sql). I did find out that the problem is in Oracle. The same where code, that works for one of my source ORACLE databases, fails on the other of my source ORACLE databases.
/*** From GOOGLE:
This is an ORACLE "red herring" error that is common on some Oracle databases. A single quote or pair of single quotes needs to be two single quotes or two pairs of single quotes.
ERROR: Prepare error: ICommandPrepare:repare failed. : ORA-00907: missing right parenthesis
SQL statement: SELECT j.* FROM NCMR.VCI_NIC_NC_MAIN j where j.etq_created_date >= (to_date(01AUG2013:00:00:00,
08-18-2014 04:18 PM
Try something like this. Oracle, from memory, can cope with unambiguous date strings with hyphens and enclosed in single quotes.
%let PrevMonthStartdate = %str(%')%sysfunc(intnx(Month,"19AUG2014"d,-1,b),yymmdd10.)%str(%');
SQL: where TRXN_DATE = &prevmonthstartdate
08-18-2014 04:40 PM
If you use explicit pass thruogh in the full approach to some RDBMS do not use anything that is common to SAS language. The RDBMS does not understand SAS language or any of those references. I See: where datepart(TRXN_DATE) = '&prevmonthstartdate'd.
If you wan the find the cause break the sql up in smaller understandable pieces.
08-19-2014 01:48 AM
Rommel that is a nice link, but did you notice: "Due to incompatibility in date and time functions between ODBC and SAS, ODBC might not process them correctly". The date, time and datetime conventions in several systems are not the same. A string common to sas as '... 'd has not a meaning in Oracle. An other one often causing problems is the "reserved words" list. These are also not equal, sometimes causing surprising results.
This list of SQL-fucntions is used with implicit pass thru in optimizing the SQL language translation form SAS Ansi to a native SQL dialect.
With explicit pass thru (execute statement) you are the one coding in a native SQL dialect. SAS has his hands off.
For debugging features.:
- explain Using EXPLAIN PLAN If you want to know what happens when the sql is correct.
Do you have Oracle sql developer (free tool) you can develop and verify the Oracle Sql.