Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I attempting to create a SQL Statem

Status
Not open for further replies.

Ktwhite

Technical User
Mar 6, 2003
23
US
I attempting to create a SQL Statement within Crystal Reports 8.5. I visited a number of forums, reviewed the Crystal Knowledge Base website, and have been unable to create a successful query within Crystal Reports. I have been able to run numerous successful queries within USQL, through which we are using to connect to the database, but I have not been able to duplicate this success within Crystal. Below is a copy of the select statement which I am attempting to use within Crystal, which is actually a copy of the SQL Query which Crystal Builds that can be viewed when you select: Database>Show Crystal Query. I copied the following select statement into the SQL Expression Field within the Formula Editor just to see how Crystal would handle the syntax, thinking that; “If Crystal builds it, perhaps it would understand the syntax within the Formula Editor”. Following the select statement is the error message I am receiving. I have no idea what Crystal wants in terms of Syntax, and I am lost as how to find out. Any suggestions would be appreciated. Thanks.

SELECT
SL_RECORD.SL_FUND
FROM
kdd.SL_RECORD SL_RECORD
WHERE
SL_RECORD.SL_SUB_OBJ_CLS = SI_ACCT_RECORD.SI_ACCT_ACCT

error message:

ODBC error:[Transoft][tsodbc][usqlsd]’FROM’ expected here(,)
Ken
 
SQL expressions do not allow subqueries (I know, I know if they can do other things why not this..). What they are designed for is passing functions to the DB to calculate rather than crystal.

For instance I have a number field stored as a string (a whole nother question) that they want to report on as a range. You can do this in crystal, but crystal will transfer all the records and the crystal will eliminate the ones that don't work..

Instead I create a SQL statement:

Convert(int, field)

I can use that SQL statement in the select formula and crystal will use it.

You can do all sorts of things..
Conversions/string manipulation/math/date functions/case statements..

You just can't create a complete subselect.

Lisa
 
Thanks so much for your response. I will review the on-line help to more familiarize myself regarding formulas with respect to SQL Expressions. One more thing...The reason I have been looking into SQL Expressions, is because of some linking problems. I am linking from one primary table to two look up tables. The primary table is a relationship table, and in the two look up tables, one contains customer name, and the other contains customer address information. I can only pull data from one of the look up tables at a time. Why this is happening is a another matter, I was just hoping to get pointed in the right direction since all the options seems so overwhelming. Ken
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top