asked on

Crystal Reports XI SQL Expression error: ORA-00936

Greetings,

I am currently experiencing problems making a very basic SQL Expression in Crystal Reports XI. Here is the query:

SELECT PATIENT."PATIENT_NUMBER"
FROM "DEV"."PATIENT" PATIENT

Here is the error I get:
Error in compiling SQL Expression:
Failed to retrieve data from the database.
Details: ORA-00936: missing expression
[Database Vendor code: 936 ].

The frustrating part is that I did not write this SQL Expression myself. After trying everything I could think of while writing my own queries, I did the following: I created a new report, selected only the table I want to pull a field from. Dragged the field onto the Details portion of the Report on the Design screen. Went to Database > Show Sql Query ... on the menu. Copied the SQL query. Deleted the field from the report. Created an SQL Expression and pasted in the query.

I'm sure there's something I'm missing but I'm all out of ideas.

We're running an Oracle 9 database

ASKER CERTIFIED SOLUTION

crgary_tx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

frodoman

Does this statement execute if you try it directly on the database (using TOAD, etc.?). My guess is that it doesn't because the quotes around "PATIENT" are throwing off the parser whether the field you're going after is in the patient table or in the table aliased as patient.

Take off all of the quotation marks and it should execute just fine.

frodoman

mikovia

ASKER

Gary, you were right. SQL Expressions were not what I wanted to use.

frodoman: I tried every combination of quotations I could think of and they just won't work with SQL Expressions.

It turns out that in order to run my query I need to create a Command through the Database Expert. I pasted the query into there and it runs just fine.

Gary: I still don't understand what SQL Expressions are for. Could you give an example of a use for them?

Thanks