Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Crystal Reports XI SQL Expression error: ORA-00936

Posted on 2007-07-24
3
Medium Priority
?
4,809 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:mikovia
3 Comments
 
LVL 13

Accepted Solution

by:
crgary_tx earned 375 total points
ID: 19555930
You cannot use the select statement in the SQL expression formulas. SQL expressiions are similar to formulas where you can define some  the sql fucntions. Then these sql expression formulas can then be referred in the selection formulas the advantage being the record selection is pushed to the server for definite. Also you can use the sql expression formula as the field. The field is precalculated on the server side.

hth..
Gary
0
 
LVL 42

Expert Comment

by:frodoman
ID: 19555947
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
0
 

Author Comment

by:mikovia
ID: 19556143
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
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question