CODEWITHSUNDEEP
sqlexceloracle-database
user3735564
user3735564

Reputation: 75

Oracle Query in Excel works, but not in SQL

I'm currently trying to query an Oracle server and pull in data into a SQL server.

Now what I've got is this query:

select * from openquery(databasename, 'SELECT * FROM RICALM.REQUEST_TIMESTAMP_EXT DueDate
WHERE DueDate.NAME = "com.ibm.team.apt.attribute.constraintdate"')

If I toss just the query:

SELECT * FROM RICALM.REQUEST_TIMESTAMP_EXT DueDate
WHERE DueDate.NAME = 'com.ibm.team.apt.attribute.constraintdate'

into excel, where I've created an oracle link, I can pull the data just fine.

However, when I pull the data in SQL, I can pull the whole table if I just do "SELECT * FROM RICALM.REQUEST_TIMESTAMP.EXT" but when I add in the WHERE clause I get an error message:

OLE DB provider "OraOLEDB.Oracle" for linked server "databasename" returned message "ORA-00972: identifier is too long".

Any workaround for this? I'd like to just schedule a query like this to run each night so I don't have to deal with refreshing an excel table.

Thanks!

Upvotes: 0

Views: 258

Answers (1)

troy
troy

Reputation: 422

Try this for the openquery you are using:

select * from openquery(databasename, 'SELECT * FROM RICALM.REQUEST_TIMESTAMP_EXT DueDate WHERE DueDate.NAME = ''com.ibm.team.apt.attribute.constraintdate''')

Difference #1 being replacing the double quotes:

"

with two single quotes, which should resolve to a single quote:

''

Difference #2 being putting it as one line.

Upvotes: 1

Related Questions