Solved

SQL Expression for 8.5

Posted on 2004-10-15
9
1,440 Views
Last Modified: 2008-02-26
I challenge any EEE to please provide me with a tried and proven simple Oracle select statement in a SQL Expression Editor that returns a sum() via a direct Crystal Reports Oracle Server database connection.

e.g.    select sum(amount_balance) from accounting_finrep  

I am using Crystal Reports 8.5 Developers edition on Oracle 9.2.0.1.0

I have read all recommendations in the entire universe regarding this problem and none of them work.
I continually receive ORA-????? errors without end.  Please help.

Thank Yee.
0
Comment
Question by:Spareyarn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12322985
I don't have Oracle installed locally, but you should be able to use:

(
SELECT DISTINCT
  SUM(account_balance)
FROM
  accounting_finrep
)

Copy the above expression and paste it into your query editor (SQL+, Toad, Embarcadero, etc..).  If the query runs in the editor, it should be good in Crystal Reports.  One key factor is that you have to encapsulate this type of query in parentheses within the SQL Expression Editor.

~Kurt
0
 

Author Comment

by:Spareyarn
ID: 12323217
The SQL syntax you provided is correct and works fine in Toad, PL/SQL Developer, SQL*Plus etc...  
However, the syntax does not compile in the SQL Expression editor in CR8.5.

I receive "ORA-01007 variable not in select list" error message, then a subsequent CR error message stating invalid sql-cannot compile.  

I have tried many options trying to get this to work, encapsulation with parenthesis, {}, " "...and none work.  I either recieve ora-00904 or ora-01007.  Other than the SQL Expression editor, CR8.5 works fine with the Oracle database views, tables, joins and stored procedures.

One other note, the "account_balance" is a field in the report JIC that was your next suggestion.

Thanks for the help, I'll keep trying the same thing over and over again...it has to work eventually ;->
0
 
LVL 13

Expert Comment

by:EwaldL
ID: 12323397
you can't use a select statement in an sql expression. sql expression can only manipulate fields, they can't return whole rows. they can be used for instance for concat or trim.

here is more info
http://support.businessobjects.com/communityCS/TechnicalPapers/sqlexpressions.pdf.asp
0
Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12323662
ewaldl, that's incorrect.  I use Select statements in SQL Expressions all the time.

You CAN use select statements in SQL Expressions.  In fact, you can have a highly complex and convoluted SQL statement.  The caveats are that you must encapsulate the Select statement in parentheses and the SQL Expression can only return a single distinct value.  A SQL Expression is passed to the database as a subquery field in the SELECT clause of the Crystal SQL statement.

Check out the following thread:

http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21161031.html

Spareyarn,  how are you connecting to the database?  Merant ODBC, Native Drivers, etc...  SQL Expressions depend upon the database connection.

~Kurt
0
 

Author Comment

by:Spareyarn
ID: 12323743
Native Oracle Server drivers provided with CR8.5

p.s.  thanks for saving me the longwinded response I was cooking up for ewaldl.
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 50 total points
ID: 12324236
Have you tried switching drivers?  You're getting oracle-specific errors, which could indicate issues with the driver.  Then again, I've found SQL Expressions to be kind of iffy with Oracle in some cases.

What is the report data source?  A View, Stored Procedure, Tables, etc...

~Kurt
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 50 total points
ID: 12324275
Also, here's a Business Objects KB article which discusses the error message you're getting, specific to your version of Crystal Reports:

http://support.businessobjects.com/library/kbase/articles/c2012294.asp

~Kurt
0
 

Author Comment

by:Spareyarn
ID: 12324868
Unfortunately, I don't have a choice when it comes to what db driver to use.  The report, once finished, will be plugged into an ERP Application (Industrial and Financial Systems-IFS) that only supports direct Oracle connections, not ODBC.  

The article you posted looks promising, I will apply CR8.5 SP3 and see what happens.

Thanks for your help.

0
 

Author Comment

by:Spareyarn
ID: 12324915
Case Closed!!!

SP3 fixed it, thank you very much.  I can now sleep a happy man.  

Probably should've looked in more detail for CR8.5 updates, instead of syntax validation on the good ole world wide web.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sum not calculating correctly 22 58
Summarizing a percent by a grouped category 14 40
Dates formatted Differently on another pc 2 35
Getting Maximum Score for certain records 7 22
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 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