Time Fields using SQL

[Scottcom4]

Hi Guys,

I am linked to oracle tables and am attempting to retireve the contents of said table using SQL. My problem is that I can't get date/times to work. By saying this I mean that I recieve the following error whenever I attempt to access date/time data.

ERROR: Prepare error: ICommandPrepare::Prepare failed.
SQL statement SELECT
TransactionsTable.TransactionID,
TransactionsTable.CheckID,
TransactionsTable.UpdateTime

FROM
TransactionsTable

The below code is what I am attempting to run. Does anyone know how I can complete this task successfully.

Thank you for your help.


PROC SQL;
CONNECT TO oledb AS oradb
(User=XXXXXXX Password=XXXXXXX DataSource=XXXXXXXXXXXXXXX provider=MSDAORA.1 );;
CREATE table CmovTrans_Dat
AS SELECT * FROM connection to oradb
(SELECT
TransactionsTable.TransactionID,
TransactionsTable.CheckID,
TransactionsTable.UpdateTime

FROM
TransactionsTable
);
DISCONNECT FROM oradb;
run;

[sbb]

It's not clear to me, but are you able to execute successfully with the most basic connection to your Oracle environment from SAS. Also, consider your SAS version and service pack maintenance level - I found several hits on the SAS support website, using the Google advanced search argument below:


proc sql oracle prepare error site:sas.com


Scott Barry
SBBWorks, Inc.

[Scottcom4]

Hi Scott,

Yes you are correct in your understanding, I am able to access the Oracle tables using Proc SQL, however as soon as I add a time or date variable I begin to get errors. My work around for the date issue was to use to_char(Varname1), howeever this did not work for the time and date variables. I gather there is a better alternative to using to_char, however at this stage it works as desired.

Thank you for the google search criteria.

Regards,
Scott

[LinusH]

Your example shows SQL pass-thru. What happends if you use LIBANME instead?

/Linus