Help with SQL expression

[Katy44]

Hi,

I am trying to write an SQL Expression in Crystal 9, linking to an Oracle database. It is a simple select statment, but I keep getting ORA-00936, missing expression error. I think this is because the field I'm selecting is called "Value", which is a reserved word. What can I do to get round this?

 

[Reebo99]

Could you post your entire expression please?

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Katy44]

I have figured out that I needed to put the entire thing in brackets, which seemed to work. So my question has moved on to, once I have got the expression with no errors:

(select description
from table1
where domain='DOMAIN1'
and value = '100')

what can I do with it? I have tried using it in my selection criteria,and also just putting it on my report. I am just playing around at the moment, trying to find out how it work, but eventually I want to be able to replace the '100' with a field.

 

[Katy44]

I forgot to mention - when I put it on the report I get 'Failed to open a rowset'.
Does anyone know any good articles about working with SQL expression fields?

 

[lynchg]

When you say that you 'put it on the report', what does that mean? You can create a SQL command in Crystal by going into the Database Expert, creating a connection to your database, and the double-clicking on the Add Command icon. Then enter your SQL onto the pop-up form, save it, and choose to add that command to your report.

If the report already has another datasource (some table or query that appears in the right-hand box in the Database Expert), you would probably need to go to the Set Datasource Location screen and switch from the old datasource to your new SQL command.

Then you need to put at least one textbox on the report that is tied to a field in the datasource. In your case you would open the Field Explorer while in design mode of your report, and then drag and drop your description field onto the detail section of your report.

If that SQL command is something that actually retrieves some data from the table then you will get a report to print with at least something on it.

 

[Katy44]

Amazing! Thank you so much. That is exactly what I wanted; the ability to create a view within Crystal rather than in the database itself!

 

[eaglejohn]

I have a similar problem as kate44, however I am running Crystal 8.0. It doesn't look as if 8.0 has a the Database expert option.

The following works, but I want to replace 45663 with a Global variable that I created in a formula.

(select RESULT."ENTRY"
from result
where RESULT."TEST_NUMBER" = 45663
and RESULT."NAME" = 'RSD')

Any help much appreciated.