I attempting to create a SQL Statem

[Ktwhite]

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

 

[lyanch]

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

 

[Ktwhite]

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