SQL Expression returning too many results

[Former Member]

I am trying to display the value of a field in one table as a caption in the report header. This caption is above a u2018descriptionu2019 field in the details section. When I run my report, I get the description field displaying 832 times; which is the number of rows found in my caption table. Here is the structure of the two tables:

Table 1: Code, Description

Table 2: Table, Name, Field Name, Caption

Here are the SQL Expressions that I have tried:

(SELECT Caption FROM Table2

WHERE Table2.FieldName='Description'

AND Table2.TableName='Code')

(SELECT DISTINCT Caption FROM Table2

WHERE Table2.FieldName='Description'

AND Table2.TableName='Code')

And

(SELECT TOP 1 Caption FROM Table2

WHERE Table2.FieldName='Description'

AND Table2.TableName='Code')

If I run these directly in my Db, I get correct results. If in Crystal, incorrect results. I have tried the following suppress on the Details: Table1.Code= previous (Table1.Code) which works UNLESS I have any summaries. Once I include a summary, my total ALWAYS factors in the suppressed, duplicate records.

I have now moved to try using parameters and a sub report for each caption; however my existing reports have sub reports that also need caption changes and I am not aware of a way to add a subreport to a subreport

Any ideas?

[former_member11696]

Do not use SELECT statements in SQL Expressions, they were not designed to to do this. Use a command object to filter the data.

[Former Member]

can you use select distinct on all the queries,

crystal tends to duplicate

create a group for your distinct value

place the records in the group header and it will return the first unique value it hits.

[Former Member]

Thanks for the reply. Unfortunately, neither the select distinct or select top 1 worked. I also tried to place in the group header; however some of my reports have 3 or 4 groups and this did not work here either