Issue Using SQL Expressions in Crystal with an Ingres Database

[BeeBear2]

Hi,

I have recently started building Crystal Reports using an INGRES database unfortunately. I am having a LOT of trouble trying to even get the most basic SELECT statement to work.

Can anyone tell me what form of SQL I should be using to make this work?? Or why it isnt working

I am using Crystal 11.5.12 and I am pretty sure I am using INGRES V 10.0.

I've tried using Oracle/SQL as used previously on another database, but that doesnt work, and nor does PSQL which according to the INGES company website is what you should use.


HELP!!! I miss writing my SQL Expressions.

If you need other info, please let me know

 

[CoSpringsGuy]

Can you explain what sort of errors or issues you are seeing?
And just to get an idea of your CR experience level where are you entering your SQL statement in CR?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[kenhamady]

I assume you mean a SQL Expression field and not a SQL Command. SQL Expression fields were designed for simple expressions, not SELECT statements, but you can do a SELECT if you follow a few of the undocumented rules, like:

Make sure the SQL only returns one column and one row of data​

Put the entire expression in a single pair or parens​

This article has an example and the links in it might also help:

http://kenhamady.com/cru/archives/2692

To know the type of SQL to use for your database, I would create a simple report using 2 tables and pull one field from each table to put on the report. Then go into the database menu and select SHOW SQL QUERY. That will be an example of working SQL for this connection. If you are using ODBC the SQL will likely be different from what you use within the database.


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[BeeBear2]

Sorry, I've been offline for a while.

Hi CoSpringsGuy - My Crystal knowledge is that I have been writing Crystal Reports for nearly 20 years.
I am trying to write SQL Statements from the SQL Expression Fields in the Field Explorer tab in crystal. I have done this previously with no problem, but as I say, that was using an ODBC Database.

I get varied error messages depending on what I put in -

Hi Kenhamady,
As an example, here is the sql I got using 2 tables from the database, selecting fields from both tables, and using a filter:

SELECT "cases"."case_id", "cases"."created_dt", "case_services"."service_date"
FROM "ingres"."cases" "cases" INNER JOIN "ingres"."case_services" "case_services" ON "cases"."case_id"="case_services"."service_id"
WHERE ("cases"."case_id">=5000 AND "cases"."case_id"<=5400)

 

[kenhamady]

There is always the possibility that your non-ODBC connection doesn't support the subqueries generated by SQL Expressions. Not all do.

But the answer to your first question ("what type of SQL should I use?") is the SQL shown in your last post. Follow that syntax along with the rules that I mentioned. The "why it isn't working?" question is impossible to answer without a specific example of an expression you tried and the corresponding error message that you received for that specific example.

Just for fun, see if this works as a SQL Expression if you copy it and paste it into a new SQL Expression:

(SELECT max(service_date)
FROM case_services
WHERE case_services.case_id = 5000)

Make sure you try it once exactly as written above before you start tweaking it. I am sure it isn't close to what you need, but it might get you started in the right direction. If you can't get that to work try using a single field from the report as your expression. Double click any field from the report field list, save the expression and see if that runs.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[kenhamady]

And the last resort is to skip the expression and convert the entire report to SQL. Then you can write the SQL Expression as a subquery directly.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[BeeBear2]

Hi Ken,

So I tried using the exact SQL you put in your response, and got that great generic error message "Error in compiling SQL Expression:"
I then realised the WHERE Clause wasn't correct, so fixed that, as below:
(SELECT max(service_date)
FROM case_services
WHERE case_services.service_id = 5000) (The "Case_ID" in the Case_Services table is called "Service_ID" just for fun!


and then got a different error message (see attached JPG file)

I actually only want to use the SQL Query to add detail to the current Crystal Report, without restricting the general selection criteria, or adding alias tables for multiple join points, so I'd prefer to not use the SQL query builder to create the reports.

I might just have to wait until the helpdesk get around to converting the database from Ingres to Oracle.......

 

[BeeBear2]

Oops! The attachment didnt upload - here it is

 

[kenhamady]

It looks like it is complaining about the MAX function. The SQL in a SQL Expression has to return only one record, and without a summary function that is hard to ensure. Not sure what else to suggest.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[BeeBear2]

Thanks for your help Ken - looks like it might just have to wait for the update :-(