I am having an issue trying to add a sub-query, a command or SQL expression to a report built by a user in Crystal 10.  I need to add a field from a table not referenced in the main report. Also, the tables are not the same data types so I cannot add them in the database expert. The script I am trying to add as either a command or SQL: 

 

(SELECT

P2.LAST_NAME,

P2.FRST_NAME

 

FROM

PRSN  P2,

PYM_KEYS PK

 

WHERE

P2. APPL_ID=(CONVERT(INT, PK.KEY_VAL)) AND

PK.KEY_TYPE=18)

 

I have tried several derivatives of this with single and double quotes around the fields and with/without parenthesis but nothing seems to compile.

 

Basically I need to pull in a name field for each detail row returned. The PYM_KEYS table is referenced in the main report but PRSN is not.

 

The most frequent error messages are: Error in compiling SQL Expression: Incorrect Syntax near ‘LAST_NAME’.

 

Failed to open rowset.  Cannot perform an aggregate function on a column from a table not declared in the same subquery.

 

Does anyone have any ideas for way to create this field?  I have gotten it to work by writing the whole report as an SQL command but the user would prefer the report remain in Crystal framework with only the field as SQL script so that the report can be modified easily.  Thank you for your help with this!